How to do a multi SUMIF

[jray9242]

I have a need to do the following

Sheet: Checklisting

A B C D E F

<table border="0" cellpadding="0" cellspacing="0" width="532"><colgroup><col style="mso-width-source:userset;mso-width-alt:1170;width:24pt" width="32"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6838;width:140pt" width="187"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:24pt" height="17" width="32">
</td> <td class="xl66" style="width:48pt" width="64">Date</td> <td class="xl66" style="border-left:none;width:140pt" width="187">Description</td> <td class="xl67" style="border-left:none;width:66pt" width="88">Amount</td> <td class="xl67" style="border-left:none;width:68pt" width="91">Balance</td> <td class="xl66" style="border-left:none;width:53pt" width="70">Code</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Jan</td> <td class="xl65" style="width:48pt" width="64">1/2/2011</td> <td class="xl64" style="width:140pt" width="187">xxxxxxxxxxx</td> <td class="xl69" style="width:66pt" width="88">$12.00 </td> <td class="xl69" style="width:68pt" width="91">$4.00 </td> <td class="xl70">Misc</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Jan</td> <td class="xl65" style="width:48pt" width="64">1/2/2011</td> <td class="xl64" style="width:140pt" width="187">xxxxxxxxxxxx</td> <td class="xl69" style="width:66pt" width="88">$12.00 </td> <td class="xl69" style="width:68pt" width="91">$4.00 </td> <td class="xl70">Misc</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Feb
</td> <td class="xl65" style="width:48pt" width="64">2/2/2011</td> <td class="xl64" style="width:140pt" width="187">xxxxxxxxxxxxxxxx</td> <td class="xl69" style="width:66pt" width="88">$12.00 </td> <td class="xl69" style="width:68pt" width="91">$4.00 </td> <td class="xl70">Food</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Feb</td> <td class="xl65" style="width:48pt" width="64">2/2/2011</td> <td class="xl64" style="width:140pt" width="187">xxxxxxxxxxxxxxxx</td> <td class="xl69" style="width:66pt" width="88">$12.00 </td> <td class="xl69" style="width:68pt" width="91">$119.29 </td> <td class="xl70">Misc</td> </tr> </tbody></table>


<table style="width: 397px; height: 90px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="3" width="70"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:53pt" height="17" width="70">Code Yearly
</td> <td class="xl68" style="width:53pt" width="70"> Jan</td> <td class="xl68" style="width:53pt" width="70"> Feb</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt" height="17"> Misc 36.00
</td> <td class="xl65" style="border-left:none" align="right">-$24.00</td> <td class="xl65" style="border-left:none" align="right">$12.00</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">Food 12.00
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">$0.00</td> <td class="xl65" style="border-top:none;border-left:none" align="right">$4.00</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">
=SUMIF(F$2:F$787,H2,D$2:D$787) This works great for a yearly total but I can't seem to get it to sum by the Month. Can you please help me?

Thank you,

Jim </td> <td class="xl65" style="border-top:none;border-left:none" align="right">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">


</td> </tr> </tbody></table>

 

[danzon]

use SUMPRODUCT instead

something like

=SUMPRODUCT(--(MONTH(B$2:B$787)=1),--(D$2:D$787),--(F$2:F$787="Food"))


for the sum of all FOOD in JAN

 

[texasalynn]

try this

Excel Workbook
	I	J	K	L
1	Code Yearly	Yearly	Jan-2011	Feb-2011
2	Misc	$36.00	$24.00	$12.00
3	Food	$12.00	$0.00	$12.00
Sheet3

Excel 2003

Cell Formulas
Range	Formula
J2	=SUMIF(F$2:F$787,I2,D$2:D$787)
K2	=SUMPRODUCT(--(MONTH($B$2:$B$287)=MONTH(K$1)),--(YEAR($B$2:$B$287)=YEAR(K$1)),--($F$2:$F$287=$I2),($D$2:$D$287))
L2	=SUMPRODUCT(--(MONTH($B$2:$B$287)=MONTH(L$1)),--(YEAR($B$2:$B$287)=YEAR(L$1)),--($F$2:$F$287=$I2),($D$2:$D$287))

 

[T. Valko]

I have a need to do the following

Sheet: Checklisting

A B C D E F

<TABLE cellSpacing=0 cellPadding=0 width=532 border=0><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 24pt; HEIGHT: 12.75pt" width=32 height=17>

</TD><TD class=xl66 style="WIDTH: 48pt" width=64>Date</TD><TD class=xl66 style="BORDER-LEFT: medium none; WIDTH: 140pt" width=187>Description</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 66pt" width=88>Amount</TD><TD class=xl67 style="BORDER-LEFT: medium none; WIDTH: 68pt" width=91>Balance</TD><TD class=xl66 style="BORDER-LEFT: medium none; WIDTH: 53pt" width=70>Code</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Jan</TD><TD class=xl65 style="WIDTH: 48pt" width=64>1/2/2011</TD><TD class=xl64 style="WIDTH: 140pt" width=187>xxxxxxxxxxx</TD><TD class=xl69 style="WIDTH: 66pt" width=88>$12.00 </TD><TD class=xl69 style="WIDTH: 68pt" width=91>$4.00 </TD><TD class=xl70>Misc</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Jan</TD><TD class=xl65 style="WIDTH: 48pt" width=64>1/2/2011</TD><TD class=xl64 style="WIDTH: 140pt" width=187>xxxxxxxxxxxx</TD><TD class=xl69 style="WIDTH: 66pt" width=88>$12.00 </TD><TD class=xl69 style="WIDTH: 68pt" width=91>$4.00 </TD><TD class=xl70>Misc</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Feb

</TD><TD class=xl65 style="WIDTH: 48pt" width=64>2/2/2011</TD><TD class=xl64 style="WIDTH: 140pt" width=187>xxxxxxxxxxxxxxxx</TD><TD class=xl69 style="WIDTH: 66pt" width=88>$12.00 </TD><TD class=xl69 style="WIDTH: 68pt" width=91>$4.00 </TD><TD class=xl70>Food</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>Feb</TD><TD class=xl65 style="WIDTH: 48pt" width=64>2/2/2011</TD><TD class=xl64 style="WIDTH: 140pt" width=187>xxxxxxxxxxxxxxxx</TD><TD class=xl69 style="WIDTH: 66pt" width=88>$12.00 </TD><TD class=xl69 style="WIDTH: 68pt" width=91>$119.29 </TD><TD class=xl70>Misc</TD></TR></TBODY></TABLE>


<TABLE style="WIDTH: 397px; HEIGHT: 90px" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" span=3 width=70></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="WIDTH: 53pt; HEIGHT: 12.75pt" width=70 height=17>Code Yearly

</TD><TD class=xl68 style="WIDTH: 53pt" width=70>Jan</TD><TD class=xl68 style="WIDTH: 53pt" width=70>Feb</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="HEIGHT: 12.75pt" height=17>Misc 36.00

</TD><TD class=xl65 style="BORDER-LEFT: medium none" align=right>-$24.00</TD><TD class=xl65 style="BORDER-LEFT: medium none" align=right>$12.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>Food 12.00

</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>$0.00</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>$4.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>
=SUMIF(F$2:F$787,H2,D$2:D$787) This works great for a yearly total but I can't seem to get it to sum by the Month. Can you please help me?

Thank you,

Jim
</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>

</TD><TD class=xl65 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>



</TD></TR></TBODY></TABLE>

What version of Excel are you using?

 

[jray9242]

Sorry, I should have posted that. I am using Excel 2007.

I will try these and let you know how it goes.

Thanks,

Jim

 

[jray9242]

danzon,

I used your formula. This worked just fine for me.

texasalynn,

I will test yours because I like to have more than one way to do things.

Thanks again all for the help!

Jim

 

[T. Valko]

Sorry, I should have posted that. I am using Excel 2007.

I will try these and let you know how it goes.

Thanks,

Jim

Try this...

Assuming you want to sum the Amount column.

Book1
	A	B	C	D	E	F
1	Month	Date	Description	Amount	Balance	Code
2	Jan	1/2/2011	xxxxxxxxxxx	$12.00	$4.00	Misc
3	Jan	1/2/2011	xxxxxxxxxxxx	$12.00	$4.00	Misc
4	Feb	2/2/2011	xxxxxxxxxxxxxxxx	$12.00	$4.00	Food
5	Feb	2/2/2011	xxxxxxxxxxxxxxxx	$12.00	$119.29	Misc
CheckListing

Book1
	A	B	C	D
1	Code	Yearly	Jan	Feb
2	Misc	36	24	12
3	Food	12	0	12
Summary

Formula entered on the Summary sheet in cell B2:

=SUMIF(CheckListing!F$2:F$5,A2,CheckListing!D$2:D$5)

Formula entered on the Summary sheet in cell C2 and copied across to D2:

=SUMIFS(CheckListing!$D$2:$D$5,CheckListing!$A$2:$A$5,C$1,CheckListing!$F$2:$F$5,$A2)

Then, select the range B2:D2 and copy down as needed.

 

[jray9242]

Valko,

You guys rock! It's been a long time since I have had to do this stuff and I am finding this place is fantastic to glean knowledge.

Thanks again for the help and this now opens me up to be able to do a lot of other similar sheets.

One last question Valko, how did you get the example to look like the spreed sheet? I could use this stye next time I post a question.

Thanks again!

Jim

 

[T. Valko]

Valko,

You guys rock! It's been a long time since I have had to do this stuff and I am finding this place is fantastic to glean knowledge.

Thanks again for the help and this now opens me up to be able to do a lot of other similar sheets.

You're welcome. Thanks for the feedback!

One last question Valko, how did you get the example to look like the spreed sheet? I could use this stye next time I post a question.

Thanks again!

Jim

I use this add-in:

http://www.excel-jeanie-html.de/index.php?f=1

It makes things a lot easier to understand when you can see the data and how it is formatted.

 

[jray9242]

Thanks again and the website is now bookmarked!

Jim