Hi there,
I am trying to do a sum with 3 different criteria. If you open up the attached spreadsheet if will hopefully make more sense. What I want to do is a sum if the top row equals 5, the send row equals 2, and the person is a particular number in column A, for this example let's pick out "PERSON 1".
So I should get an answer that is 12+3+7=22. How on earth do I do this. I would really appreciate your help and thanks in advance!
DIFFICULT SUM.xlsx
Try the formula below in cell M3, then copy down
=SUMPRODUCT(($B$1:$L$1=5)*($B$2:$L$2=2)*(B3:L3))
Last edited by ncmay; 06-12-2013 at 07:17 PM. Reason: added a word
Awesome, thanks so much ncmay! Just a quick question, is it possible to do a lookup on "Person 1" rather than the reference to the cells of that person?
Last edited by martinpgibson; 06-12-2013 at 07:26 PM.
Try this...
A15 = Person 1
=SUMIFS(INDEX(B3:L10,MATCH(A15,A3:A10,0),0),B1:L1,5,B2:L2,2)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
array formula
{=SUM(IF(B1:L1&B2:L2&A3:A10="52person 1",B3:L10))}
Thanks Ghozi, that is a genius way of doing what I wanted to do, I have never thought about that lookup technique before!
In the attached file select the desired name from the drop down in cell A1 and the total for that person is displayed in cell B1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks