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 |
