Sum Product?

[GeneJoseph]

A B C
1 1/2/17 Meals $50
2 2/2/17 Office $100
3 Other $200

Sum C1:C3 IF B1:B3 AND IF A1:A3 occurred in 1(January). The answer should be $50. It should ONLY SUM C where B says meals (B1) and A says "1" (A1). I have tried some diff formulas but C was still being summed when A was empty although it did not when A was 2/1/17- not sure why. I only wanted it to sum row 1 (A,B,C)

 

[jtakw]

Hi,

E1 is your requested formula.
I recommend F1 formula using H1:I2 as your criterion if you might want to change what you want SUMmed.

Book1
	A	B	C	D	E	F	G	H	I
1	1/2/2017	Meals	50		50	50		month to lookup	1
2	2/2/2017	Office	100					item to lookup	meals
3		Other	200
Sheet169

Cell Formulas
Range	Formula
E1	=SUMPRODUCT((MONTH(A1:A3)=1)*(B1:B3="Meals")*C1:C3)
F1	=SUMPRODUCT((MONTH(A1:A3)=I1)*(B1:B3=I2)*C1:C3)

 

[GeneJoseph]

Hi,

E1 is your requested formula.
I recommend F1 formula using H1:I2 as your criterion if you might want to change what you want SUMmed.

	A	B	C	D	E	F	G	H	I
Meals	month to lookup
Office	item to lookup	meals
Other

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/2/2017[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2/2/2017[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet169​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A1:A3)=1)*(B1:B3="Meals")*C1:C3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A1:A3)=I1)*(B1:B3=I2)*C1:C3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, I will give it a shot !

 

[GeneJoseph]

Hi,

E1 is your requested formula.
I recommend F1 formula using H1:I2 as your criterion if you might want to change what you want SUMmed.

	A	B	C	D	E	F	G	H	I
Meals	month to lookup
Office	item to lookup	meals
Other

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/2/2017[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2/2/2017[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet169​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A1:A3)=1)*(B1:B3="Meals")*C1:C3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A1:A3)=I1)*(B1:B3=I2)*C1:C3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I tried this but it still sums C when A is empty. I only want C to sum IF B1 is meals AND A1 = month 1

 

[jtakw]

We'll add another condition:

Book1
	A	B	C	D	E	F	G	H	I
1		Meals	50		0	0		month to lookup	1
2	2/2/2017	Office	100					item to lookup	meals
3		Other	200
Sheet169

Cell Formulas
Range	Formula
E1	=SUMPRODUCT((ISNUMBER(A1:A3))*(MONTH(A1:A3)=1)*(B1:B3="Meals")*C1:C3)
F1	=SUMPRODUCT((ISNUMBER(A1:A3))*(MONTH(A1:A3)=I1)*(B1:B3=I2)*C1:C3)

 

[GeneJoseph]

thank you! I think that worked

 

[jtakw]

You're welcome.