Suming using 2 criteria

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

Sheet 1 has
A B
Bob Y
Dan
Pete
Ted Y
Mia Y
...

Sheet 2 has
A B C
Bob 10 Q
Dan 15 W
Pete 8 E
Ted 12 R
Mia 22 T
...

I want to sum(sheet2!B1:B100) where (sheet2!C="W") and (sheet1!B = "Y" where
(sheet2!A = sheet1!A))

I know it can be done but I can't figure out the formula construct.

Anyone?
 
One way:

=SUMPRODUCT(--(Sheet1!B1:B100="Y"), --(Sheet2!C1:C100="W"),
Sheet2!B1:B100)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple conditions 3
Pivot, MSQuery or crafty use of MATCH and OFFSET? 2
Scrabble Value calculation for Welsh words 0
Sumproduct function allow a lookup for one Date to check if it fallsin Date Range in other table 9
Index with mulitple value returns and muliple column returns 4
Compare 2 sheets 2
Array with many cells from a row but not in order or together 11
Columns, All Data In 5

Back
Top