Countifs With Months

[kumara_faith]

Hi,

I have the following tables:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Table 1				Table 2					Table 3
3	Branch	Sales Staff	Date		Branch	February	March	April		Branch	February	March	April
4	Texas	Mark	8/2/2022		Texas	0	0	0		Texas	1	0	0
5	Maine	Javier	7/2/2022		Maine	0	0	0		Maine	1	0	0
6	Illinois	Juan	5/4/2022		Illinois	0	0	0		Illinois	0	0	1
7	Iowa	Kenny	5/3/2022		Iowa	0	0	0		Iowa	0	1	0
8	Virginia	Joan	9/4/2022		Virginia	0	0	0		Virginia	0	0	1
Sheet1

Cell Formulas
Range	Formula
H4:J8	H4	=COUNTIFS($C$4:$C$8,$G4,$E$4:$E$8,H$3)

1) Table 1 is the raw data
2) Table 2- I am trying to summarize the count of sales staffs recorded for the branch based on the month. However my current formula is not showing the correct results
3) Table 3 - This is an example of the correct result.

Appreciate any help to modify the current formula to show the desired results.

 

[jtakw]

Hi,

One way:

Book3.xlsx
	C	D	E	F	G	H	I	J
2	Table 1				Table 2
3	Branch	Sales Staff	Date		Branch	February	March	April
4	Texas	Mark	2/8/2022		Texas	1	0	0
5	Maine	Javier	2/7/2022		Maine	1	0	0
6	Illinois	Juan	4/5/2022		Illinois	0	0	1
7	Iowa	Kenny	3/5/2022		Iowa	0	1	0
8	Virginia	Joan	4/9/2022		Virginia	0	0	1
Sheet1078

Cell Formulas
Range	Formula
H4:J8	H4	=SUMPRODUCT(($C$4:$C$8=$G4)*(TEXT($E$4:$E$8,"mmmm")=H$3))

 

[kumara_faith]

Hi ,

I am getting blanks in my file. Is there any other setting or format cells I should apply ?

Book1
	C	D	E	F	G	H	I	J
2	Table 1				Table 2
3	Branch	Sales Staff	Date		Branch	February	March	April
4	Texas	Mark	2/8/2022		Texas	0	0	0
5	Maine	Javier	2/7/2022		Maine	0	0	0
6	Illinois	Juan	4/5/2022		Illinois	0	0	0
7	Iowa	Kenny	3/5/2022		Iowa	0	0	0
8	Virginia	Joan	4/9/2022		Virginia	0	0	0
Sheet1

Cell Formulas
Range	Formula
H4:J8	H4	=SUMPRODUCT(($C$4:$C$8=$G4)*(TEXT($E$4:$E$8,"mmmm")=H$3))

 

[jtakw]

Are your H3:J3 (February, March, etc.) Real Dates formatted to show Month, or are they just plain Text ??

 

[kumara_faith]

The formatting is showing as General

 

[jtakw]

The formatting is showing as General

If they are Text, also check for leading/trailing spaces for G3:J3, and if they are Text, my formula should work, when you select any G3:J3, what do you see in the formula bar? "February" or a number?
What about your dates in Column E, are they Actual Dates, in other words, if you change their formatting to General, do you see a bunch of Numbers ??

 

[kumara_faith]

Hi,

I manually typed in the whole table in a new sheet but it still shows 0 for the result.

 

[jtakw]

Hi,

I manually typed in the whole table in a new sheet but it still shows 0 for the result.

Well, if that's the case, I'd have no idea why you're not getting the results, as it works for me.

If you want, upload your sample file to a Free hosting site (i.e. Dropbox), and post the link here, I, or someone will try to diagnose the problem.

 

[jtakw]

One more thing, are your 2 tables in the same sheet ??
Did you add Sheetname, change/adjust cell range/references accordingly ??

 

[kumara_faith]

Hi,

Here is a link to the file. Appreciate the help. Thanks.

Excel Formula.xlsx

Sheet1 Branch,Sales Staff,Date,Branch,February,March,April Texas,Mark,2/8/2022,Texas,0,0,0 Maine,Javier,2/7/2022,Maine,0,0,0 Illinois,Juan,4/5/2022,Illinois,0,0,0 Iowa,Kenny,3/5/2022,Iowa,0,0,0 Virginia,Joan,4/9/2022,Virginia,0,0,0

docs.google.com