Shortening a long SUMPRODUCT/COUNTIF formula

[willow1985]

Hello,

I have the following formula:

="Open WorkOrders: "&SUMPRODUCT(COUNTIFS('WO Report'!$E:$E,"<>History",'WO Report'!$E:$E,"<>Consumables",'WO Report'!$E:$E,"<>Quarantine",'WO Report'!$E:$E,"<>R & D / Testing",'WO Report'!$E:$E,">""",'WO Report'!$S:$S,$B$12:$G$12))

I am looking to shorten it by referencing a range of cells for: History, Consumables, Quarantine and R&D / Testing. The Range of cells would be Z16:Z19.

Is there any way to do this?

Something like: ="Open WorkOrders: "&SUMPRODUCT(COUNTIFS('WO Report'!$E:$E,"<>Z16:Z19",'WO Report'!$E:$E,">""",'WO Report'!$S:$S,$B$12:$G$12)) Although I know this is not correct....
Maybe add Z20 as a blank to get rid of: 'WO Report'!$E:$E,">""" Section if possible??

Thank you very much to anyone who can help with this

Carla

 

[Eric W]

Try:

="Open WorkOrders: "&SUMPRODUCT(COUNTIFS('WO Report'!$S:$S,$B$12:$G$12))-SUMPRODUCT(COUNTIFS('WO Report'!$S:$S,$B$12:$G$12,'WO Report'!$E:$E,$Z$16:$Z$20))

Put your values in Z16:Z19 and put an = sign in Z20. Also note that when using ranges in a COUNTIFS, you can only have 2, one horizontal ($B$12:$G$12) and one vertical ($Z$16:$Z$20).

 

[willow1985]

This is perfect! Thank you very much! And thank you for the tip! I did not know it can only have 2 ranges, a horizontal and a vertical.

Thank you again!

Carla

 

[Eric W]

Happy to help!