[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Alternative to SUMIF

Posted on 2015-01-19
Medium Priority
401 Views
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
0
Question by:Flora
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 40557391
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

LVL 12

Expert Comment

ID: 40557394
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

LVL 6

Author Closing Comment

ID: 40557395
Rory,
thanks A million.

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

LVL 6

Author Comment

ID: 40557399
Thank you James. your formula worked too.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month14 days, 20 hours left to enroll