sumif with multiple criteria

P

Pebbles

I have a huge spreadsheet (sample below) and I need to know the value of what
2 people have sold. The problem is that the men are listed along the columns
and the females are in the rows. I'm sure it's simple - I'm just mental
blocking.
The sort of results I'd expect are: Debbie and Guy have sold $12,100/George
and Rebecca have sold $3,000.
A B C D E
1 Bob John Guy George
2 Jane $100 $600 $23,000 $2,000
3 Rebecca $200 $5,000 $43,000 $3,000
4 Alison $300 $8,000 $53,000 $400
5 Clair $400 $2,000 $70,000 $5,450
6 Debbie $800 $1,000 $12,100 $8,000

Thanks
 
M

Mike H

Hi,

Try this

=INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0))

In practice I'd use cell references for the lookup values

=INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0))

When you change the range remember to change A1:E6 to the FULL table
and A1:A6 - A1:E1 to the FULL column/row

Mike
 
M

Mike H

Hi,

Because you looking for the intersect you could also do it like this

=SUMPRODUCT((A2:A6=F1)*(B1:E1=G1)*(B2:E6))

Note now how the ranges are arranged. i.e A1 which is the top left corner of
the table isn't now included in any of the ranges.

Mike
 
B

Billy Liddel

Mike

I could not get this to work but

=INDIRECT(ADDRESS(MATCH(F1,A1:A6,0),MATCH(G1,A1:E1,0)))

is ok

Regards
Peter
 
B

Billy Liddel

Hi Mike

I couldn't paste this in one cell so I had to rewrite it. I'll look at it
again. But I'd use the SUMPRODUCT anyway - that was fine

Thanks
Peter
 
T

Teethless mama

The easiest way of doing it is "Defined Names ranges" in both vertical and
horizontal data range

Formula: = Debbie Guy
 

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

Top