I need solution for sum for multiple criteria.
I have massive data to sum with multiple conditions, and I can't use "SUMPRODUCT" or SUMIFS like at below.
What I need is sum for F1,F2,G1 and G2.
=SUMIFS($B$1:$B$6,$A$1:$A$6,F1)+SUMIFS($B$1:$B$6,$A$1:$A$6,G1)+SUMIFS($B$1:$B$6,$A$1:$A$6,F2)+SUMIFS($B$1:$B$6,$A$1:$A$6,G2)
Really need to know what other solution do I have other than those.
Last edited by neovivaldi; 10-07-2015 at 05:02 PM.
Why can't you use SUMPRODUCT?
=SUMPRODUCT(((A1:A6=F1)+(A1:A6=F2)+(A1:A6=G1)+(A1:A6=G2))*(B1:B6))
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
If I don't have choice, yes I must use SUMPRODUCT but I really wonder if there is another formula to handle this problem.
I don't believe there is. This formula works but doesn't accept cell references as part of the array
=SUM(SUMIF(A1:A6,{"tomato","banana","melon","kiwi"},B1:B6))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Can I just give range as criteria?
And I know this sumifs returns "0" but I need kind of this.
=SUMIFS(B1:B6,A1:A6,F1,A1:A6,F2,A1:A6,G1,A1:A6,G2)
Because F1:G2 criteria changes often I just can't type every time when I use this formula.
Is there solution to use SUMIFS for this problem?
My best wish I could do this.
=SUMIFS($B$1:$B$6,$A$1:$A$6,$F$1:$G$2)
=SUMPRODUCT(SUMIFS(B1:B6,A1:A6,F1:G2))
It works, I don't know why.
Am I doing right?
Yes, nicely done.
Edit: You can do with SUMIF
=SUMPRODUCT(SUMIF(A1:A6,F1:G2,B1:B6))
Last edited by ChemistB; 10-07-2015 at 01:34 PM.
Thanks so much everyone.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks