Consulting

Results 1 to 2 of 2

Thread: Conditional count help

  1. #1

    Conditional count help

    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))}

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Try

    =SUMPRODUCT(--(NOT(ISERROR(SEARCH($B45,'Data - Bazaar'!$U:$U)))),--(ISNUMBER('Data - Bazaar'!$AR:$AR)))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •