PDA

View Full Version : Conditional count help



jmenche
12-04-2014, 08:54 AM
Howdy,

I've used array formulas and sumproducts to do compound sums and counts before. However, the columns always have one value in it and I can test for the positive. I have a dataset where column U has numerous values in it separated by commas. The array formula below has to test for the negative or I get an error message. Let's say column AR has numbers in it and I want to count all instances where AR>0 and the below is true too. Can that be done?

Cheers

Jeff

={SUM(IF(ISERROR(SEARCH($B45,'Data - Bazaar'!$U:$U)),0,1))}

Bob Phillips
01-03-2015, 06:14 AM
Try

=SUMPRODUCT(--(NOT(ISERROR(SEARCH($B45,'Data - Bazaar'!$U:$U)))),--(ISNUMBER('Data - Bazaar'!$AR:$AR)))