Sumif based on 2 row criteria and 1 column criteria

[Rahn111]

A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria's are met?
Thanks in advance for your help...

 

[Shrikant]

First of All, Where is the sum range?

second: All the ranges should be identical in size. e.g A1:A10 and B1:B10 are identical but 3rd is not.

One more point: Sumifs function will act as AND function. I mean if all the conditions are met then and then only it will sum.

So I would suggest this formula.

=SUMIFS(Sum_Range,Criteria Range 1, Criteria 1, Criteria Range 2, Criteria 2)*IF(ISERROR(MATCH(Criteria 3, Criteria Range 3,0)),0,1)

Still require clarification from you on your query to help you better.

 

[Aladin Akyurek]

A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria's are met?
Thanks in advance for your help...

Where do you have the criteria for A1:A10, B1:B10, and C1:E1? If the question does not look right, please elaborate?

 

[Rahn111]

Where do you have the criteria for A1:A10, B1:B10, and C1:E1? If the question does not look right, please elaborate?

A B C D E
1 Size Item April-14 May-14 June-14
2 1.75L Tequila 1 2 3
3 750ML Vodka 4 5 6
4 375ML Rum 7 8 9
5 200ML Scotch 10 11 12

I am looking for a function to read the outut (C2:E5) based on criteria 1 (A2:A5) criteria 2 (B2:B5) and criteria 3 (C1:E1)...
Example 375ML Rum Price for April should be 7... How do I get it to read properly? I used sumifs but it doesnt read Criteria 3 properly

 

[Aladin Akyurek]

A B C D E
1 Size Item April-14 May-14 June-14
2 1.75L Tequila 1 2 3
3 750ML Vodka 4 5 6
4 375ML Rum 7 8 9
5 200ML Scotch 10 11 12

I am looking for a function to read the outut (C2:E5) based on criteria 1 (A2:A5) criteria 2 (B2:B5) and criteria 3 (C1:E1)...
Example 375ML Rum Price for April should be 7... How do I get it to read properly? I used sumifs but it doesnt read Criteria 3 properly

H2: 375ML

I2: Rum

J2: April-14

K2, Control+shift+enter, not just enter:

=IFERROR(INDEX($C$2:$E$5,MATCH($H2,IF($B$2:$B$5=$I2,$A$2:$A$5),0),
  MATCH($J2,$C$1:$E$1,0)),"")

You might get away with SumIfs if the price records are unique... In K2 just enter:

=SUMIFS(
  INDEX($C$2:$C$5,0,MATCH($J2,$C$1:$E$1,0)),
  $A$2:$A$5,$H2,
  $B$2:$B$2,$I2)

 

[Peter_SSs]

You might get away with SumIfs if the price records are unique... In K2 just enter:

=SUMIFS(
  INDEX($C$2:$C$5,0,MATCH($J2,$C$1:$E$1,0)),
  $A$2:$A$5,$H2,
  $B$2:$B$2,$I2)

I don't understand that statement, though I also don't understand the formula.

It seems to me that SUMIFS is perfectly feasible. What have I missed?
For the same layout:

=SUMIFS(INDEX($C$2:$E$5,0,MATCH(J2,$C$1:$E$1,0)),$A$2:$A$5,H2,$B$2:$B$5,I2)

There's also:

=SUMPRODUCT(($A$2:$A$5=H2)*($B$2:$B$5=I2)*($C$1:$E$1=J2),$C$2:$E$5)

 

[Aladin Akyurek]

If you correct for the typo, c2:c5, to c2:e5, all will be intelligible.

 

[Peter_SSs]

If you correct for the typo, c2:c5, to c2:e5, all will be intelligible.

You may note that in my SUMIFS formula I had corrected C2:C5 to C2:E5 (and also the B2:B2 to B2:B5).

The 'statement' that I was saying I also didn't understand was ..

You might get away with SumIfs if the price records are unique

I presume you mean so long as, say, 375ML Rum doesn't occur twice in columns A:B. However, if they are price records for a particular date, I can't see that is likely and if it did happen we don't know which record would be required to be extracted for that criteria anyway.

Anyway, hopefully the OP returns and can find something suitable from what has been offered.