Countifs forumla

ohoward

Hi, I am looking at doing a countifs forumla across 7 different sheets.

=countifs({spreadsheet 1}, "Green",{spreadsheet 2}, "Green",{spreadsheet 3}, "Green",{spreadsheet 4}, "Green",{spreadsheet 5}, "Green",{spreadsheet 6}, "Green",{spreadsheet 7}, "Green",)

Is this the correct formula? As it is not picking up the right amount - Thank you

DKazatsky2

Hi @ohoward,

The formula is trying to reconcile a case where all conditions are true at the same time, which is most likely why you are not getting the correct value.

Try something like this instead.

=COUNTIF({spreadsheet 1}, "Green") + COUNTIF({spreadsheet 2}, "Green") + COUNTIF({spreadsheet 3}, "Green") + COUNTIF({spreadsheet 4}, "Green") + COUNTIF({spreadsheet 5}, "Green") + COUNTIF({spreadsheet 6}, "Green") + COUNTIF({spreadsheet 7}, "Green")

Hope this helps,

Dave

RDRGSJ00

Hello ohoward,

As far as I know, you cannot reference multiple sheets in a single function. Read this:

https://community.smartsheet.com/discussion/107877/can-you-reference-different-sheets-in-a-formula#:~:text=You%20cannot%20reference%20multiple%20sheets,single%20column%20references%20for%20example).

Could you elaborate a little more? Are you expecting back a number? You could pull back the data into Helper columns and then add or count the data on the main sheet.

DKazatsky2

Hi @ohoward,

The formula is trying to reconcile a case where all conditions are true at the same time, which is most likely why you are not getting the correct value.

Try something like this instead.

=COUNTIF({spreadsheet 1}, "Green") + COUNTIF({spreadsheet 2}, "Green") + COUNTIF({spreadsheet 3}, "Green") + COUNTIF({spreadsheet 4}, "Green") + COUNTIF({spreadsheet 5}, "Green") + COUNTIF({spreadsheet 6}, "Green") + COUNTIF({spreadsheet 7}, "Green")

Hope this helps,

Dave

ohoward

@DKazatsky2 It worked thank you so much