Can I use Find in a count formula?

Jeff Smith

I have a spreadsheet that is a calendar for consultants. I have the primary column using the consultants name as the Grandparent, the Month as the Parent and the weeks of the month as the children.

• Consultant
•         Month

•              Week
• I have another column that shows availability using RYGG for different status'. 
• What I want to do is come up with a formula that shows for the month of Feb I have X number of Red, Yellow, Green and Grey balls. 
• The reason I'm having to do a find is that the weeks are set up as Feb 1st, Feb 8th, etc. 

My question is can I put a find parameter in a count formula?

J. Craig Williams

Yes.

 

 

Columns "Primary Column" and "Status" header is cut off.

 

In each of the children of the count section, the formula used COUNTIFS and FIND

 

For Green Feb 1st, it looks like this:

 

=COUNTIFS([Primary Column]$38:[Primary Column]$45, [Primary Column]29, Status$38:Status$45, [Primary Column]$28)

 

Two criteria for the COUNTIFS.

1. Does the Primary Column match the "Green" from [Primary Column]29.

2. Does the Status column match the Primary Column to the left 

(to avoid hard-coding "Green", "Yellow", etc..)

That is, the "Green"'s look at $28, the "Yellow"'s look at $31, and so on.

 

For each of the counting items, only need to change the reference to the color.

I would add another column to avoid that too.

 

Enjoy.

 

Craig

 

 

 

J. Craig Williams

(I have the indent on Consulant B wrong, but only cosmetic for the example)

 

Craig

Jeff Smith

Perfect. Thanks so much.