Sum ifs to combine same data into one cell

Hello,

I'm working on an expense/mileage report and I need to be able to combine any duplicates from my columns S & T and add them to my columns W & X. These will total with the sum if formula already in column Y. As you can see from my example codes and amounts (in red). 710 and 6730.00 both should be combined together in W & X to add both amounts together as shown in column Y. Is there a formula (like another sumif) that I can use to do this automatically for me? See attached file.

As always thank you for any help you can provide.

How are you deriving the unique Project code in column W? Is this done manually? The same with the Cost code?

You can use this formula in W21:

=SUMIFS($U$21:$U$34,$S$21:$S$34,W21,$T$21:$T$34,X21)

then copy down as required.

Hope this helps.

Pete

Yes I normally do this manually. But the formula didn't work. It returned 0. Wouldn't using W21 make the formula not work since it's in the same column as the formula itself? I just noticed that the formula you gave me is the same one as the one already in column Y.

Yes, it should go into Y21. I don't know why it should return 0 for you - I got the numbers expected.

Pete

Is what I needed in this report not possible?

You can use this array* formula in cell W21:

=IFERROR(INDEX($J$21:$J$34,MATCH(0,COUNTIF(W$20:W20,$J$21:$J$34),0)),"")

and this array* formula in X21:

=IFERROR(INDEX($L$21:$L$34,MATCH(0,COUNTIF(X$20:X20,$L$21:$L$34),0)),"")

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter instead of the usual Enter.

Then you can copy them down to row 34 to get those unique values that you seem to be asking for.

Hope this helps.

Pete

Yessss!!!! This is what I needed. You are the best. thank you so much Pete

Well, glad we finally got there, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hello, I had to re-open this thread because I just realized that the formulas given work to some extent but don't work as they should for every scenario. I highlighted the discrepancies where the numbers should match and they don't. I am aware that I need to use the CTRL+SHIFT+ENTER. Please see attached.

Hello, can someone please help me to figure this out? Please see attached from the above file.

T21=IFERROR(INDEX(J$21:J$34,SMALL(IF(FREQUENCY(IF(J$21:J$34<>"",MATCH($J$21:$J$34&$K$21:$K$34,$J$21:$J$34&$K$21:$K$34,0)),ROW(J$21:J$34)-ROW(J$21)+1),ROW(J$21:J$34)-ROW(J$21)+1),ROWS($T$20:T20))),"")

Control+shift+enter

copy across and down

V21=SUMPRODUCT(($J$21:$J$34=$T21)*($K$21:$K$34=$U21)*($C$21:$I$34))

Copy down

OH WOW... you are amazing. I see that you deleted the first box. I guess it wasn't needed anymore. Thank you so much. Can I ask how do you read these formulas in plain English? They are so long and I'd really like to learn how you came up with that.

oh ok. Now, I just realized that if someone submits both an expense report and a mileage report at the same time, the totals in column V would need to be included from the mileage rate column (Q)...how would I tweak that in the column V formula?

v21=SUMPRODUCT(($J$21:$J$34=$T23)*($L$21:$L$34=$U23)*($C$21:$I$34))+SUMPRODUCT(($J$21:$J$34=$T23)*($L$21:$L$34=$U23)*($Q$21:$Q$34))

copy down

That didn't work. The previous formulas worked perfectly. I just needed to include column Q to it. I'm sorry.