Sum of data based on category in a given date range

[excel19]

Hello,

I am looking for a way to quickly sum data based on a category within a given date range. Assume date ranges are in columns A and B, category is in column C, and amount is in column D.

Thanks,

Saul

 

[T. Valko]

Hello,

I am looking for a way to quickly sum data based on a category within a given date range. Assume date ranges are in columns A and B, category is in column C, and amount is in column D.

Thanks,

Saul

Can you post about 10-15 rows of sample data and let us know what result you expect?

 

[MrVillareal]

Hi friend

Hope to add some help

Try this one

Excel Workbook
	A	B	C	D	E	F	G	H
1	Raw Data
2	Date	Category	Amount		Start	End	Category	Amount
3	8/08/2011	Category1	100		8/08/2011	14/08/2011	Category1	500
4	9/08/2011	Category2	200		8/08/2011	14/08/2011	Category2	700
5	10/08/2011	Category3	300		8/08/2011	14/08/2011	Category3	1600
6	11/08/2011	Category1	400
7	12/08/2011	Category2	500
8	13/08/2011	Category3	600
9	14/08/2011	Category3	700
Sheet28

Excel 2010

Cell Formulas
Range	Formula
H3	=SUMIFS($C$3:$C$9,$A$3:$A$9,">="&E3,$A$3:$A$9,"<="&F3,$B$3:$B$9,G3)
H4	=SUMIFS($C$3:$C$9,$A$3:$A$9,">="&E4,$A$3:$A$9,"<="&F4,$B$3:$B$9,G4)
H5	=SUMIFS($C$3:$C$9,$A$3:$A$9,">="&E5,$A$3:$A$9,"<="&F5,$B$3:$B$9,G5)

You can change the start date and end date as your criteria range.

Hope for your feedback

 

[excel19]

MrVillareal was MUCH faster at posting sample data. THANKS! I'll try that.

Here is what my sample data looks like (*I hope this works*)

<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:84pt" height="20" width="112">Start Date</td> <td class="xl65" style="border-left:none;width:92pt" width="123">End Date</td> <td class="xl65" style="border-left:none;width:80pt" width="107">Category</td> <td class="xl65" style="border-left:none;width:80pt" width="106">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">1/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">1/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,656.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">2/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">2/28/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 201.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">3/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">3/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,984.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">4/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">4/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 305.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">5/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">5/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 2,045.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">6/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">6/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 189.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">7/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">7/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 2,653.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">8/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">8/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">9/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">9/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Other</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 100.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">10/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">10/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">11/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">11/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 300.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">12/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">12/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 300.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">1/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">1/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">2/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">2/28/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">3/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">3/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 298.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">4/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">4/30/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,656.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">5/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">5/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Other</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,210.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">6/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">6/30/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,695.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">7/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">7/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 125.00 </td> </tr> </tbody></table>

I would like to quickly add each total for each category.


Saul

 

[MrVillareal]

Yeah tnx

Try this one

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Start Date	End Date	Category	Amount		Start Date	End Date	Category	Amount
2	1-Jan-09	31-Jan-09	Income	1,656.00		1-Jan-09	31-Dec-09	Income	14,356.00
3	1-Feb-09	28-Feb-09	Overtime	201.00		1-Jan-09	31-Dec-09	Overtime	695.00
4	1-Mar-09	31-Mar-09	Income	1,984.00		1-Jan-09	31-Dec-09	Other	100.00
5	1-Apr-09	30-Apr-09	Overtime	305.00		1-Jun-09	31-Jul-10	Bonus	725.00
6	1-May-09	31-May-09	Income	2,045.00
7	1-Jun-09	30-Jun-09	Overtime	189.00
8	1-Jul-09	31-Jul-09	Income	2,653.00
9	1-Aug-09	31-Aug-09	Income	3,009.00
10	1-Sep-09	30-Sep-09	Other	100.00
11	1-Oct-09	31-Oct-09	Income	3,009.00
12	1-Nov-09	30-Nov-09	Bonus	300.00
13	1-Dec-09	31-Dec-09	Bonus	300.00
14	1-Jan-10	31-Jan-10	Income	3,009.00
15	1-Feb-10	28-Feb-10	Income	3,009.00
16	1-Mar-10	31-Mar-10	Overtime	298.00
17	1-Apr-10	30-Apr-10	Income	1,656.00
18	1-May-10	31-May-10	Other	1,210.00
19	1-Jun-10	30-Jun-10	Income	1,695.00
20	1-Jul-10	31-Jul-10	Bonus	125.00
Sheet29

Excel 2010

Cell Formulas
Range	Formula
I2	=SUMIFS($D$2:$D$20,$C$2:$C$20,H2,$A$2:$A$20,">="&F2,$B$2:$B$20,"<="&G2)
I3	=SUMIFS($D$2:$D$20,$C$2:$C$20,H3,$A$2:$A$20,">="&F3,$B$2:$B$20,"<="&G3)
I4	=SUMIFS($D$2:$D$20,$C$2:$C$20,H4,$A$2:$A$20,">="&F4,$B$2:$B$20,"<="&G4)
I5	=SUMIFS($D$2:$D$20,$C$2:$C$20,H5,$A$2:$A$20,">="&F5,$B$2:$B$20,"<="&G5)

 

[Aladin Akyurek]

MrVillareal was MUCH faster at posting sample data. THANKS! I'll try that.

Here is what my sample data looks like (*I hope this works*)

<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:84pt" height="20" width="112">Start Date</td> <td class="xl65" style="border-left:none;width:92pt" width="123">End Date</td> <td class="xl65" style="border-left:none;width:80pt" width="107">Category</td> <td class="xl65" style="border-left:none;width:80pt" width="106">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">1/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">1/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,656.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">2/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">2/28/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 201.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">3/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">3/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,984.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">4/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">4/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 305.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">5/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">5/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 2,045.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">6/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">6/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 189.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">7/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">7/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 2,653.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">8/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">8/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">9/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">9/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Other</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 100.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">10/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">10/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">11/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">11/30/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 300.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">12/1/2009</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">12/31/2009</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 300.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">1/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">1/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">2/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">2/28/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 3,009.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">3/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">3/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Overtime</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 298.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">4/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">4/30/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,656.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">5/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">5/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Other</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,210.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">6/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">6/30/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Income</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 1,695.00 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;border-top:none; width:84pt" height="20" width="112">7/1/2010</td> <td class="xl67" style="border-top:none;border-left:none;width:92pt" width="123">7/31/2010</td> <td class="xl65" style="border-top:none;border-left:none;width:80pt" width="107">Bonus</td> <td class="xl68" style="border-top:none;border-left:none;width:80pt" width="106"> $ 125.00 </td> </tr> </tbody></table>

I would like to quickly add each total for each category.


Saul

Have you tried to build a pivot table to obtain the category totals?

 

[Jadegirl]

That is pretty cool. Can you explain how the &F2 and &G2 works in this formula?

in the formula:

=SUMIFS($D$2:$D$20,$C$2:$C$20,H2,$A$2:$A$20,">="&F2,$B$2:$B$20,"<="&G2)

Thanks

 

[MrVillareal]

Sure

F2 is the column for Start Date
G2 is the column for End Date

">="&F2 means you are joining the operators "greater than or equal" with the value in F2. Is it greater than or equal the value in F2?

"<="&G2 mean you are joining the operators "less than or equal" with the value in G2. Is it less than or equal the value in G2?

Its like a true or false criteria. In our situation we have 3 criteria and all have to be true to add.

Hope this help