SumIf two axes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I sum cells in range B2:F10 based on two criteria:
* A2:A10 = "XXX"
* B1:F1 = "YYY"

Thanks,
Oscar.
 
=INDEX(B2:F10,MATCH("XXX",A2:A10,0),MATCH("YYY",B1:F1,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks, but you solution only finds value of one cell in range B2:F10. "XXX"
can be found in more thant one cell in range A2:A10 and "YYY", which can be
found in more than one cell in range B1:F1. I want to sum up values of all
cells in range B2:F10 that meet specified criteria.

Oscar

"Bob Phillips" je napisal:
 
I wondered if that might be the case, but thought, no, can't be <G>

=SUMPRODUCT((A2:A10="XXX")*(B1:F1="YYY")*(B2:F10))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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

Sumif using Arrays 2
Convert/rewrite SUMIFS formula to work in Excel 97 3
Multiple Criteria Sum 8
Function for summing or counting cells based on a specified fill color 2
SUMIF with criteria "<>" & "=" 4
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" 9
sumif when criteria range is larger but includes criteria 1
match and display 5

Back
Top