Multiple criteria countif or sumproduct

**jmerre** · 09-16-2009, 02:08 PM

Thanks for the replies. I am working through the suggestions now. I am attempting to use the first suggestion and I am getting a #Value error. I currently have

{=SUMPRODUCT((('Entire Sites - Offices - Staff'!B5:B99="Northern Oklahoma District")+('Entire Sites - Offices - Staff'!B5:B99="*Rocky Mountain District*")),--('Entire Sites - Offices - Staff'!G5:G100>0),--('Entire Sites - Offices - Staff'!G5:G83<60))}

Entered by hitting shift cnrl enter.
Does pulling data from a different worksheet cause problems?

**DonkeyOte** · 09-16-2009, 02:16 PM

No need for CSE...

Your #VALUE! error will (most likely) be result of fact your final 2 ranges are of different dimension to remainder - ie should both be G5:G99 rather than G5:G100 / G5:G83, when using double unary (--) method all ranges should be of equal dimensions.

**jmerre** · 09-16-2009, 02:43 PM

Thanks for the reply! So close now. I am now returning a 0. Everything looks good and I am wondering if the database that generates the data is giving me some kind of formatting that is causing problems. I verified that the numbers are numbers and not text. Think of anything else to double check?

Thanks again!

Current line returning 0 Should be returning 5 or more.

=SUMPRODUCT((('Entire Sites - Offices - Staff'!B5:B99="Northern Oklahoma District")+('Entire Sites - Offices - Staff'!B5:B99="Rocky Mountain District")),--('Entire Sites - Offices - Staff'!G5:G99>0),--('Entire Sites - Offices - Staff'!G5:G99<60))

**jmerre** · 09-16-2009, 02:50 PM

Thanks, it seems to be working now. I copied and pasted the same line into a different cell and it seems to be working just fine. Thanks for all the help. This is a great forum.