Solved

Alternative to SUMIF

Posted on 2015-01-19
4
224 Views
Last Modified: 2015-01-19
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
Comment
Question by:Flora
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
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

by:James Elliott
Comment Utility
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")))

Open in new window


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

Author Closing Comment

by:Flora
Comment Utility
Rory,
thanks A million.

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

Author Comment

by:Flora
Comment Utility
Thank you James. your formula worked too.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now