Count occurrences occurring by Month, Quarter & Year

[jolthof7]

Hello-
I have 2 columns
Column A list of dates
Column B color type

I need to determine how many times red shows up in a month, then quarter, then year.

I'm stumped.

Thanks much!

 

[jim may]

In your Column B are the cells colored using 1) Format, Fill Color or 2) Conditional Formatting?

 

[Joe4]

One way is to use COUNTIFS. See here: https://support.office.com/en-us/ar...ondition-976d0074-245d-49e6-bf5f-1207983f82ed
You can add another condition to that formula for a particular color.

 

[jolthof7]

No formatting in Column B. Its just an entry of Red, Green or Blue.
Essentially, I need to know how many reds were in Jan 2018, Feb 2018, Mar 2018 etc..., then how many blues Jan 2018, Feb 2018, Mar 2018 etc.... etc.
Then i need to know the same but by Quarter, then Year
thank you!

 

[jim may]

So your Column B is all and only text?

 

[DRSteele]

I mocked this up for you.

Copy the formulas in Columns F H and J downwards.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I	J
1	date	colour		red	month	count	quarter	count	year	count
2	1/01/18	red			1	2	1	2	2018	6
3	1/17/18	green			2	0	2	2	2019	1
4	2/02/18	orange			3	0	3	1
5	2/18/18	yellow			4	1	4	2
6	3/06/18	orange			5	1
7	3/22/18	yellow			6	0
8	4/07/18	red			7	0
9	4/23/18	blue			8	1
10	5/09/18	red			9	0
11	5/25/18	blue			10	0
12	6/10/18	blue			11	1
13	6/26/18	green			12	1
14	7/12/18	orange
15	7/28/18	yellow
16	8/13/18	orange
17	8/29/18	red
18	9/14/18	blue
19	9/30/18	yellow
20	10/16/18	green
21	11/01/18	red
22	11/17/18	blue
23	12/03/18	red
24	12/19/18	yellow
25	1/04/19	red
26	1/20/19	green
27	2/05/19	orange
Sheet53

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--($B$2:$B$27=$D$1)*(YEAR($A$2:$A$27)=I2))
H2	=SUMPRODUCT(--($B$2:$B$27=$D$1)*(ROUNDUP(MONTH($A$2:$A$27)/3,0)=G2))
F2	=SUMPRODUCT(--($B$2:$B$27=$D$1)*(MONTH($A$2:$A$27)=E2))
A3	=A2+16

[/FONT]

 

[jolthof7]

WOW! Everyone is so nice offering me support. I should have been more clear in my question. I need a formula for everything in yellow. Link is to a pic in my shutterfly. not sure how to attach here within the text.

https://pix.sfly.com/_mM0DE

 

[jolthof7]

I mocked this up for you.

Copy the formulas in Columns F H and J downwards.

	A	B	C	D	E	F	G	H	I	J
1	date	colour		red	month	count	quarter	count	year	count
2	1/01/18	red			1	2	1	2	2018	6
3	1/17/18	green			2	0	2	2	2019	1
4	2/02/18	orange			3	0	3	1
5	2/18/18	yellow			4	1	4	2
6	3/06/18	orange			5	1
7	3/22/18	yellow			6	0
8	4/07/18	red			7	0
9	4/23/18	blue			8	1
10	5/09/18	red			9	0
11	5/25/18	blue			10	0
12	6/10/18	blue			11	1
13	6/26/18	green			12	1
14	7/12/18	orange
15	7/28/18	yellow
16	8/13/18	orange
17	8/29/18	red
18	9/14/18	blue
19	9/30/18	yellow
20	10/16/18	green
21	11/01/18	red
22	11/17/18	blue
23	12/03/18	red
24	12/19/18	yellow
25	1/04/19	red
26	1/20/19	green
27	2/05/19	orange

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet53​

Worksheet Formulas Cell Formula J2 =SUMPRODUCT(--($B$2:$B$27=$D$1)*(YEAR($A$2:$A$27)=I2)) H2 =SUMPRODUCT(--($B$2:$B$27=$D$1)*(ROUNDUP(MONTH($A$2:$A$27)/3,0)=G2)) F2 =SUMPRODUCT(--($B$2:$B$27=$D$1)*(MONTH($A$2:$A$27)=E2)) A3 =A2+16 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi DrSteel,
Im having trouble with the months and quarters. the formula is reading 1 as January but adding both 2018 and 2019 january together. I need these separated. The same thing is occurring with Quarters.
thank you!!!!