• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple criteria matching multiple criteria

M

mrzoogle

Member
Hi all,


I am trying to match multiple criteria based on multiple criteria and return a sum value on that. I can't use sumifs formulae because the multiple criteria is in one column. Please look at the sample data below:


Raw

Name Cost

A 14

B 156

C 564

D 312

E 21

F 224

G 157


Result

Name Cost

A 734

B

C


Am I suppose to use an array formula to do the trick ?


Looking forward to hear suggestions from you all soon.


Regards,

Z.
 
You didn't tell us what the criteria is, hence we have no idea where the 734 comes from. At first glace, it appears to just be the first 3 numbers, but again, I have no idea why. Please state what the actual criteria(s) are, and then we should be able to help.
 
My apologies, formatting issues.


The 734 is the sum of A,B,C costs respectively (14,156,564).


So for example if the criteria are C,E,F the sum of the cost should be 809.


Please let me know if this is not clear.


Thanks.
 
Let's say your criteria are listed in a Range called CriteriaList, and your starting data is in A2:B100.


=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A100,CriteriaList,0))),B2:B100)
 
Hi Luke M,


You are awesome as always! I've got few questions though if you don't mind.


The above formulae does not sum if the criteria is stated twice,


For example,


If there is A & A in CriteriaList the sum should be 28.


What if I just want to count the CriteriaList ?


Sorry for asking too many questions. I really feels like I need to learn hard on array formuale. It would be awesome if you could provide me a guideline as well.


Thank you very much for your time.


Regards,


Z.
 
Hmm. If the Raw Data is sorted, you could use this array:

=SUM(LOOKUP(CriteriaList,A2:B100))

Also, CriteriaList can not contain blanks.


Formula works by doing a lookup on each value in the criteria list (thus, building an array equal in size to CriteriaRange) and then returning the SUM on the array of numbers generated. Formula also works for criteria that has been duplicated.


Of course, you could also just have a helper column to the criteria values,with some sort of lookup formula and then SUM that.
 
You must log in or register to reply here.
Back
Top