SUMIFS with two Criteria in Same Field

[LearnMeExcel]

Hi Gurus

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Item	Value		Item	From	To
2	01/01/2011	N 3	42		N 1	01/02/2011	30/04/2011
3	11/01/2011	N 1	63		N 2
4	21/01/2011	N 3	11
5	31/01/2011	N 4	61		For One Criteria in Field Item	59
6	10/02/2011	N 3	63		For Two Criteria in Field Item	0	269
7	20/02/2011	N 3	71			269
8	02/03/2011	N 1	49
9	12/03/2011	N 2	92
10	22/03/2011	N 1	10
11	01/04/2011	N 2	81
12	11/04/2011	N 4	57
13	21/04/2011	N 2	37
14	01/05/2011	N 4	100
15	11/05/2011	N 3	51
Sheet3

Excel 2010

Cell Formulas
Range	Formula
F5	=SUMIFS(C2:C15,B2:B15,E2,A2:A15,">="&F2,A2:A15,"<="&G2)
F6	=SUMIFS(C2:C15,B2:B15,E2,B2:B15,E3,A2:A15,">="&F2,A2:A15,"<="&G2)
F7	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B15,E2:E3,0))),--(A2:A15>=F2),--(A2:A15<=G2),C2:C15)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

this is my data
in CEll F6 i used this formula
=SUMIFS(C2:C15,B2:B15,E2,B2:B15,E3,A2:A15,">="&F2,A2:A15,"<="&G2)
the result zero
cuz i check the N1 and N2 in Sames Formula
but it works when i have two Criteria for date
here is the formula in F5
=SUMIFS(C2:C15,B2:B15,E2,A2:A15,">="&F2,A2:A15,"<="&G2)
why it doesn't work ?

 

[Marcelo Branco]

Hi,

Maybe

=SUMPRODUCT(SUMIFS(C2:C15,B2:B15,E2:E3,A2:A15,">="&F2,A2:A15,"<="&G2))

HTH

M.

 

[Aladin Akyurek]

F6, just enter:

=SUMPRODUCT(SUMIFS(C2:C15,B2:B15,E2:E3,A2:A15,">="&F2,A2:A15,"<="&G2)

As you see, E2:E3 is specified as a condition set for B2:B15. This set up yields effectively an OR-object. SumProduct collects (totals) them together.

ISNUMBER(MATCH(B2:B15,E2:E3,0)) has the same effect: an OR-object. [1]

If you list each criterion separately as you have done, we get an AND-specification, where a B-cell supposed to satisfy E2 as wel as E3.

Note. It would be interesting to know the history of spreadsheet expressions like this one: When, by whom, etc.

 

[LearnMeExcel]

Thanx Marcelo
and thanx Aladin
now it is clear for me, my Problem with type of condition AND , OR
with Date the condition was OR
but with Item the condition was AND
from this pionts : SUMIFS work with conditions OR if Criteria in same Field

you are so great Aladin

but sorry i can't get your idea from this Note

Note. It would be interesting to know the history of spreadsheet expressions like this one: When, by whom, etc.

 

[Aladin Akyurek]

Thanx Marcelo
and thanx Aladin
now it is clear for me, my Problem with type of condition AND , OR
with Date the condition was OR
but with Item the condition was AND

The two date conditions are in fact yielding an AND-object: Thus, a date in the date range must satisfy >= F2 and <= G2 at the same time like in:

2 >= 1 and 2 <= 3

2 meets the two conditions at the same time.

from this pionts : SUMIFS work with conditions OR if Criteria in same Field

Yep...

you are so great Aladin

Thanks for providing feedback and compliment...

but sorry i can't get your idea from this Note

That was a side note for a call that somebody should write a spreadsheet history.