COUNTIF based on data in two columns

[RodneyC]

This may be hard to explain and hopefully the attached image will help.

I have about 66,000 lines of data on a tab named WO. I need to COUNTIF and SUMIFS on a tab named CAT.

Column C on the WO tab contains a list of names. I'm using colors in the example but across the 66,000 lines there are probably 17000 different names so the formula needs to include a cell reference not a name reference. In the example, you will see there are 13 lines with the value Green. If I was to do a simple COUNTIF, the result would be 13. However, Green is also connected to the values in column D. There are four different values associated with Green: Breakfast, Lunch, Dinner and Dessert.

I need a COUNTIF formula that will count the total as 4 not 13. This is important as another column is calculating averages and it needs to be based on 4, not 13.

Thank you in advance

WOave.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1							Column D on CAT tab	Column E on CAT tab	Column F on CAT tab
2
3		Color	Meal	Cost				Count	Sum	Average
4		Green	Breakfast	$12			Green	13	$255	$20
5		Green	Breakfast	$7			Blue	2	$26	$13
6		Green	Breakfast	$16			Red	1	$264	$264
7		Green	Breakfast	$8			Orange	2	$51	$26
8		Green	Lunch	$14
9		Green	Lunch	$18
10		Green	Lunch	$21			What I want
11		Green	Lunch	$9			Green	4	$255	$64
12		Green	Lunch	$37			Blue	2	$26	$13
13		Green	Lunch	$25			Red	1	$264	$264
14		Green	Lunch	$26			Orange	2	$51	$26
15		Green	Dinner	$17
16		Green	Dessert	$45
17		Blue	Breakfast	$11
18		Blue	Lunch	$15
19		Red	Dinner	$65
20		Red	Dinner	$46
21		Red	Dinner	$76
22		Red	Dinner	$77
23		Orange	Breakfast	$12
24		Orange	Breakfast	$16
25		Orange	Lunch	$23
26
WO

Cell Formulas
Range	Formula
I4	I4	=COUNTIF(C4:C25,H4)
J11:J14,J4:J7	J4	=SUMIFS($E$4:$E$25,$C$4:$C$25,H4)
K11:K14,K4:K7	K4	=J4/I4

 

[FlameRetired]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								Column D on CAT tab	Column E on CAT tab	Column F on CAT tab
2
3			Color	Meal	Cost				Count	Sum	Average
4			Green	Breakfast	12			Green	13	255	19.61538
5			Green	Breakfast	7			Blue	2	26	13
6			Green	Breakfast	16			Red	1	264	264
7			Green	Breakfast	8			Orange	2	51	25.5
8			Green	Lunch	14
9			Green	Lunch	18
10			Green	Lunch	21			What I want
11			Green	Lunch	9			Green	4	255	63.75
12			Green	Lunch	37			Blue	2	26	13
13			Green	Lunch	25			Red	1	264	264
14			Green	Lunch	26			Orange	2	51	25.5
15			Green	Dinner	17
16			Green	Dessert	45
17			Blue	Breakfast	11
18			Blue	Lunch	15
19			Red	Dinner	65
20			Red	Dinner	46
21			Red	Dinner	76
22			Red	Dinner	77
23			Orange	Breakfast	12
24			Orange	Breakfast	16
25			Orange	Lunch	23
Sheet3

Cell Formulas
Range	Formula
I4	I4	=COUNTIF(C4:C25,H4)
J4:J7,J11:J14	J4	=SUMIFS($E$4:$E$25,$C$4:$C$25,H4)
K4:K7,K11:K14	K4	=J4/I4
I11:I14	I11	=SUMPRODUCT(SIGN(FREQUENCY(IF(C$4:C$25=H11,MATCH(D$4:D$25,D$4:D$25,0)),ROW(D$4:D$25)-ROW(D$4)+1)))

You may need to enter the I11 formula with Control+Shift+Enter on your version of Excel. I'd also worry a bit about performance with 66K rows.

Very clever. I never thought about using SIGN function as a condition in that construction. I had always forced a 1 in an IF function. This is a keeper.