Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Can I use Find in a count formula?

Jeff Smith
edited 12/09/19 in Archived 2016 Posts

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?

Comments

  • J. Craig Williams
    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

     

     

     

    SS_COUNTIFS.jpg

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

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

     

    Craig

  • Perfect. Thanks so much. 

This discussion has been closed.