+ Reply to Thread
Results 1 to 3 of 3

Countifs alternative on 2003 with multiple cells and worksheets

  1. #1
    Registered User
    Join Date
    09-14-2008
    Location
    Australia
    Posts
    11

    Countifs alternative on 2003 with multiple cells and worksheets

    I created the following in Excel 2007 but need to come up with a replacement formula in 2003.

    =COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")

    With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.

    In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.

    Will really appreciate any suggestions.
    Last edited by Ex0dus; 05-18-2009 at 10:44 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Countifs alternative on 2003 with multiple cells and worksheets

    Your suggested SUMPRODUCT should work though you don't need the double unary and you may have issues re: "0"

    Re: double unary in your sample you could remove & use:

    =SUMPRODUCT(('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp"))
    (the multiplication of the results will coerce the Booleans)

    Re: "0"
    in a COUNTIF with a criteria of "0" both 0 as number and 0 as text will be included in the COUNT whereas in a SUMPRODUCT only 0 as text will be counted... thus if 'Data Low'!AB2:AB5000 contain numbers as numbers & not as text strings you won't get a result and in that case you should use:

    =SUMPRODUCT(('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000=0)*('Data Low'!AC2:AC5000="Non Corp"))

    If you wanted to opt for double unary approach over * method:

    =SUMPRODUCT(--('Data Low'!AA2:AA5000="No"),--('Data Low'!AB2:AB5000=0),--('Data Low'!AC2:AC5000="Non Corp"))

    However, what I would say is you're going to need 3 SUMPRODUCTS for each calculation... each referencing a big ish range... performance will be affected.

    I would personally advocate you create a concatenation string of the fields of interest on each source sheet in a separate column, you can then use a standard COUNTIF which will be significantly more efficient... eg

    'Data Low'!AZ2 = $AA2&":"&$AB2&":"&$AC2
    copy down to AZ5000

    Then instead of the above SUMPRODUCT you can simply use:

    =COUNTIF('Data Low'!$AZ$2:$AZ$500,"No:0:Non Corp")

    much, much, much more efficient and obviously compatible with all versions.
    Last edited by DonkeyOte; 05-18-2009 at 03:35 AM.

  3. #3
    Registered User
    Join Date
    09-14-2008
    Location
    Australia
    Posts
    11

    Re: Countifs alternative on 2003 with multiple cells and worksheets

    Thank you very much. That did the trick.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1