Countif function with multiple sheets

[christianbiker]

Can I use the "countif" function using a group of sheets as the range and the criteria on a separate sheet or manually typed in?

I have tried and continue to get the #VALUE error.

OR

Can I do the same thing and perform the "countif" function by using a specific value as the way to count?

Thanks...

 

[Aladin Akyurek]

thanks...

i have previously done both of your suggestions, excluding the sumproduct function, and continue to receive the same error. this is a couple of the formulas i have tried:

{=SUMPRODUCT(COUNTIF(INDIRECT("Day "&{25,26}&"!E8:E20"),K4))}
{=SUMPRODUCT(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))}
{=SUMCOUNTIF(INDIRECT("Day "&{25,26}&"!E8:E20"),K4))}
{=SUM(COUNTIF(INDIRECT("Day"&{25,26}&"!E8:E20"),K4))}

this is the original formula that i started with and received #VALUE! error:
=COUNTIF('Day 1:Day 31'!E8:E24,'Monthly Summary'!K4)

The formulas with SumProduct do not require control+shift+enter.

You need to observe the fact that the sheets such a formula refers to must exist. Check this...

The formula:

=COUNTIF('Day 1:Day 31'!E8:E24,'Monthly Summary'!K4)

is wrong/illegal (hence #VALUE!) because CountIf does not operate on 3d data.

 

[christianbiker]

i had already tried the sumproduct formula with & without the {} signs and neither worked. same error. and the sheets do exist...31 of them in this format Day 1, Day 2, etc.

i also tried using the sheet name as it appears in VBA but that doesn't work.

i have tried what i know so far and honestly don't completely understand how all the formulas are or aren't supposed to work.

what i really need to know is what will/does work vs. what doesn't work.


thanks

 

[Aladin Akyurek]

i had already tried the sumproduct formula with & without the {} signs and neither worked. same error. and the sheets do exist...31 of them in this format Day 1, Day 2, etc.

i also tried using the sheet name as it appears in VBA but that doesn't work.

i have tried what i know so far and honestly don't completely understand how all the formulas are or aren't supposed to work.

what i really need to know is what will/does work vs. what doesn't work.


thanks

This

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Day "&{25,26}&"'!E8:E20"),K4))

should work as intended.

 

[christianbiker]

it didn't work. it is telling me that the formula i typed contains an error however i simply pasted what you had provided to me:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Day "&{25,26}&"'!E8:E20"),K4))

 

[Aladin Akyurek]

it didn't work. it is telling me that the formula i typed contains an error however i simply pasted what you had provided to me:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Day "&{25,26}&"'!E8:E20"),K4))

No wonder...

=SUMPRODUCT(COUNTIF(INDIRECT("'Day "&{25,26}&"'!E8:E20"),K4))

 

[christianbiker]

AWESOME!!!

it works perfectly...thanks for your help and time!!!