CountIFs question

**Ryuujin** · 06-12-2012, 06:49 AM

hey ive got a working formula but now i need it to get date from more then 1 sheet and am having trouble

Please Login or Register  to view this content.

I want it to check maybe 5 other sheets as well as 1 they are named 2,3,4 and 5

**daddylonglegs** · 06-12-2012, 07:10 AM

Try this version

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&{1,2,3,4,5}&"'!E6:E200"),"Sign On",INDIRECT("'"&{1,2,3,4,5}&"'!F6:F200"),"Yes"))

**Ryuujin** · 06-12-2012, 07:16 AM

returns a #REF error

**daddylonglegs** · 06-12-2012, 07:22 AM

it works when I test it - do you have 5 sheets called 1,2,3,4 and 5? If not you'll get an error.......

this version will cope with some or all of the sheets being missing

=SUMPRODUCT(IFERROR(COUNTIFS(INDIRECT("'"&{1,2,3,4,5}&"'!E6:E200"),"Sign On",INDIRECT("'"&{1,2,3,4,5}&"'!F6:F200"),"Yes"),0))