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
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 |