Total cells within a Column using IF, DATERANGE & TEXT

[msi56]

Hello,

I am working on a records document that will be added to over time and will need to generate automatic reports.

I've simplified the format for what questions I need answered, but see below the basic information 'category', 'units' & 'date':



What I need to report is the total units by month, per category. So for example I would like one cell to total all 'mens' 'units' within the month of July.

As this will be added to over time, the formula will need to pick up new data being added within all subsequent rows.

Total value is irrelevant at this stage, once I figure how to total these values I can apply this to other cells.

Appreciate your help.

Mitch

 

[jtakw]

Hi,

It would be better to show a sample table rather than a screenshot, so potential helpers wouldn't need to replicate your table.

This might be what you're after; however, looking at your screenshot, your C or "Date" column may not be formatted as a date, so the formula may need to be tweaked (and also, you said July, but your table shows "Aug-16", so that's what I used), but try:

Excel 2010
	A	B	C	D	E	F	G	H
1	Category	Units	Date		Aug-16	Gender	Units	Total Value
2	MENS	2,775	5/07/2016			MENS	0
3	MENS	3,015	5/07/2016			LADIES	2415
4	MENS	2,220	5/07/2016			KIDS	2835
5	MENS	888	5/04/2016
6	MENS	660	5/04/2016
7	MENS	804	5/04/2016
8	LADIES	1,455	15/05/2016
9	LADIES	1,245	15/08/2016
10	LADIES	1,170	15/08/2016
11	KIDS	1,485	15/08/2016
12	KIDS	1,350	15/08/2016
13	KIDS	1,395	15/05/2016
14
15			Assumes this column is formatted as Date, d/mm/yyyy		Assumes the Header is formatted as mmm-yy
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--(A$2:A$13=F2)*(MONTH(C$2:C$13)=MONTH(E$1)),B$2:B$13)

G2 formula copied down.

 

[msi56]

That's been a great help - thanks!

 

[jtakw]

That's been a great help - thanks!

You're welcome, welcome to the forum.