Function Question in Excel

I have attached the spreadsheet for your reference. I am wanting to use the sumifs function, but have not been able to get it to work. I’m fine with using named ranges, arrays, etc. My goal is to not have to create another column or sheet to get the calculation to work. I designed a diagram on sheet three to show what I am trying to do.  Any guidance you have for this I would greatly appreciate the help.
Function-Question.xlsx
karaflanaganAsked:
Who is Participating?
 
barry houdiniCommented:
You can use this "array formula" in C2

=SUM(IF(Sheet1!B$2:B$5=B2,IF(ISNUMBER(MATCH(Sheet1!A$2:A$5,IF(Sheet2!B$2:B$4=A2,Sheet2!A$2:A$4),0)),Sheet1!C$2:C$5)))

confirmed with CTRL+SHIFT+ENTER

see attached

regards, barry
27419472.xlsx
0
 
StephenJRCommented:
I would add state as a column in your first table (so in D2 and down =VLOOKUP(A2,Sheet2!$A$2:$B$4,2,FALSE)) and then you can use

=SUMPRODUCT((Sheet1!$D$2:$D$5=A2)*(Sheet1!$B$2:$B$5=B2)*(Sheet1!$C$2:$C$5))
0
 
karaflanaganAuthor Commented:
I am reviewing these options and will respond shortly.  Thanks for the help!
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.

All Courses

From novice to tech pro — start learning today.