Summing of one field where multiple fields have to be similar

I have a spreadsheet with 4 fields. One of the fields is numeric.

If all three of the fields (string) are equal to another line, then I want to add up the estimated numbers. Basically what I want to be left with is unique values for the three string fields with a total estimated number. There is no logic to what the values in the three string fields can be - they just have to be the same, and there could be any number of records in addition.

I have been wrestling with doing loops, or even a pivot table, but am not getting exactly what I am looking for.

Any help would be greatly appreciated. I have attached a spreadsheet.

Eric

You can put this formula in D2 of your Desired sheet:

=SUMIFS(Original!D:D,Original!A:A,A2,Original!B:B,B2,Original!C:C,C2)

then copy down.

Hope this helps.

Pete

Somebody with more experience in VBA can probably offer an elegant solution, and I'll try and experiment more on my own.....

But I was wondering.... Can you possibly accomplish a similar result with the built in sumifs formula? For example, based on your sample, this formula provides a value of 36 for the

=SUMIFS(D2:D22,A2:A22,"1a",B2:B22,"blue",C2:C22,"wet")

I would think possible a combination of getting all the unique value for the first 3 columns and then sum if on each combination through a loop may do the trick.... But i'm still no expert at this stuff. I'll try and experiment here, but someone else may have a better, faster answer.

I didn't realise that you wanted to generate the list as well - here's a simple formula approach:

Put this formula in E2 of the Original sheet:

=IF(COUNTA(A2:C2)=0,"",IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,MAX(E$1:E1)+1,""))

Copy this down as far as you need to - it will result in a sequential number each time a new combination is encountered.

Then you can use this formula in A2 of the Desired sheet:

=IFERROR(INDEX(Original!A:A,MATCH(ROWS($1:1),Original!$E:$E,0)),"")

Copy this across into B2:C2, then copy A2:C2 down until you start getting blanks. Then you can use the formula I gave you earlier in D2, i.e.:

=SUMIFS(Original!D:D,Original!A:A,A2,Original!B:B,B2,Original!C:C,C2)

and copy this down.

The list appears in a different order than in your sheet, but you can always fix the values then sort the table how you wish.

Hope this helps.

Pete

Thank you all! I will dig into this tomorrow. Appreciate your help.

I spent some more time this morning via both some web searches and the macro recorder and came up with the below. This is still 'rough' with some hard coded ranges, but I believe gets the desired result.

Sample attached

Please Login or Register  to view this content.