Using COUNTIFS to count number of occurrences within two different sheets = UNPARSEABLE

Hello - This is my first time using this community. I am trying count across two sheets to count the number of times a name is present in a column. They could be contained in both or just one sheet. My formula is continually wrong and from experience I know that it is likely something little that I am missing. Here is the latest one I have tried:

=COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)), {Polaris CSC Open}, OR({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I understand what you are attempting to do, but I am not 100% confident that I interpreted which references belong to which sheet in the formula. Try this and see if it works:

    =COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)) + COUNTIFS(({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))

    The general concept here is to break your formula up into two different COUNTIFS statements. The first half of your formula seems to be exactly right. You should then essentially write the same formula for the second sheet and add the results of those two statements together.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I understand what you are attempting to do, but I am not 100% confident that I interpreted which references belong to which sheet in the formula. Try this and see if it works:

    =COUNTIFS({Polaris minus CSC Creator}, =Creator@row, {Polaris minus CSC Date}, >=DATE(2022, 1, 1), {Polaris minus CSC Date}, <=DATE(2022, 12, 31)) + COUNTIFS(({Polaris CSC Range 2}, =Creator@row, {Polaris CSC Open}, >=DATE(2022, 1, 1), {Polaris CSC Open}, <=DATE(2022, 12, 31))

    The general concept here is to break your formula up into two different COUNTIFS statements. The first half of your formula seems to be exactly right. You should then essentially write the same formula for the second sheet and add the results of those two statements together.

  • Michele R
    Michele R ✭✭
    edited 10/02/23

    Thank you Carson! This matches the formula I ended the evening with. I found the problem this morning as well, my reference sheet link somehow got mixed. THANK YOU for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!