If + sum + countiif

Hi, I'm trying to get the total cost per company- Please see attachment with sample data.
there are two sets- apples and oranges use only set1 and bananas and cherries use set 2. How can I get the sum of each unique fruit for each unique company, to get the total cost?test.xlsx

This gives the toal for each unique fruit by unique company

=SUMPRODUCT(($B$2:$B$12=B2)*($A$2:$A$12=A2)*($C$2:$C$12+$D$2:$D$12)*$E$2:$E$12)

It doesnt seem to work- Im getting different values for the same company.

and the same exact fruit?

Even though you say "Unique Fruit" do you mean Set of fruit, as in Apples and Oranges is a set and Banannas and cherries is a set?

It would probably be better to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I have the sheet from the first post but it doesn't have expected results so I guess I am missing something.

Yes- so the total for every unique fruit- I have multiple entries because the days change- but even if there are multiple apples- I want just one of it, because the cost is same. So in G2 I need 5*12 + 3*9 + 2*15 +3*10 to get the total for the first company (G3-G7 could be blank or same values.. doesnt matter). and in G8 I need 4*12 +7*15. similarly in G11 I need 11*9 + 14 *12 for company 3.
Hope that makes sense?

Okay, so it's the sum for each company (all fruits)

Try this copied down

=IF(COUNTIF($A$2:$A2,A2)=1,SUMPRODUCT(($A$2:$A$12=A2)*($C$2:$C$12+$D$2:$D$12)*$E$2:$E$12),"")

I'm trying to figure out how to remove the duplicates (i.e. row 2 and 3). That's the tricky part. You're right, I think we can work around the set A and Set B thing telling Excel to only look in col C for Apples and Oranges for example.

I've hit a different snag.

This will give me correct amounts for every company except company2.

=IF(COUNTIF($A$2:A2,A2)=1,SUMPRODUCT(--(A2=$A$2:$A$12),IF(($B$2:$B$12="apples")+($B$2:$B$12="orange"),$C$2:$C$12,$D$2:$D$12),$E$2:$E$12),"")

I keep getting 48. Can't make it budge.

Anybody see anything?

hi cs02. try this array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

copy down the cells.

Originally Posted by José Augusto

Hi Dave

You assume that your formula, in this part IF(($B$2:$B:$12)="apples" gives as a result {1,1,0,0,0,0,1,0,0,0,1} .
Excel calculates that part as just ($B$2="apples").
So, for company1 IF(($B$2:$B$12="apples")+($B$2:$B$12="orange") gives as result True+False, i.e. 1 for all formulas.

Regards

Thank you José for looking at this. I see it, now. Apples, orange, bananas and cherry are irrelevant.