# Alternative to SUMIF

Hello

please see attached file.  i need two help to simplify this formula in Column E.
i need help with using any alternative formula or existing one, that eliminates the helper column of "D".

it does not matter if the formula becomes an array formula.
ee.xlsx
LVL 6
###### Who is Participating?

Commented:
Is the formula supposed to sum column DV rather than column B in the "Dairy Products" part? If not, and it's supposed to use B as well, try:

=IF(SUMPRODUCT(--(\$A\$2:\$A\$1833&\$C\$2:\$C\$1833=A2&"Seafood"),\$B\$2:\$B\$1833)/SUMIF(A:A,A2,B:B)>=PercentageTT,"Seafood",IF(SUMPRODUCT(--(\$A\$2:\$A\$1833&\$C\$2:\$C\$1833=A2&"Dairy Products"),\$B\$2:\$B\$1833)/SUMIF(A:A,A2,B:B)>=PercentageTT,"Dairy Products",IF(SUMPRODUCT(--(\$A\$2:\$A\$1833&\$C\$2:\$C\$1833=A2&"Beverages"),\$B\$2:\$B\$1833)/SUMIF(A:A,A2,B:B)>=PercentageTT,"Beverages","Other")))
0

Managing DirectorCommented:
Try this:

``````=IF(SUMIFS(B:B,A:A,A2,C:C,"Seafood")/SUMIF(A:A,A2,B:B)>=PercentageTT,"Seafood",IF(SUMIFS(B:B,A:A,A2,C:C,"Dairy Products")/SUMIF(A:A,A2,B:B)>=PercentageTT,"Dairy Products",IF(SUMIFS(B:B,A:A,A2,C:C,"Beverages")/SUMIF(A:A,A2,B:B)>=PercentageTT,"Beverages","Other")))
``````

*btw, I think this also fixes the erroneous reference to column DV in your original formula.
0

Author Commented:
Rory,
thanks A million.

it was a typo on the formula DV is D.  so your solution worked.
0

Author Commented:
Thank you James. your formula worked too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.