+ Reply to Thread
Results 1 to 5 of 5

Sum(if()) with multiple parameters

  1. #1
    astrodon
    Guest

    Sum(if()) with multiple parameters

    I am attempting to aggregate values from a worksheet table and I need to use criteria from three columns in this array. Typically, if I wanted to return a total from Column G based on some value in Column A :

    Column A named range "PCLAS"; Column G named range "VALUE" - then
    Please Login or Register  to view this content.
    Is there a way to use multiple criteria to SUM/COUNT/IF criteria from more than one column is met?

    TIA

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can extend the formula you already have but it would probably be easier to use SUMPRODUCT which doesn't require CTRL+SHIFT+ENTER, e.g. if you want to add a criteria for range XYZ to be = "Something" you'd use

    =SUMPRODUCT(--(PCLAS = "550"),--(XYZ="Something"),VALUE)

    You can add extra criteria in the same way

    Note: typically you wouldn't use quotes around numbers.....unless they're stored as text

  3. #3
    astrodon
    Guest
    Are the '--' to be included? I looked in help and SUMPRODUCT information there is not very clear.

    Anyway I tried it both ways and still get '#REF!' return. I am using named ranges and they are all of the same length, etc.

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Yes, you need to use the --s. Look here for more information on SUMPRODUCT, various uses and syntaxes etc.

    Formula 3 looks like it should do what you want, i.e.

    =SUMPRODUCT(--(t2_tca=$B6),--(t2_pgroup="D"),(t2_acres))

    If you get #REF! that might be because you have #REF! errors in the referenced ranges....

  5. #5
    astrodon
    Guest
    Yes, you need to use the --s. Look here for more information on SUMPRODUCT, various uses and syntaxes etc.

    Formula 3 looks like it should do what you want, i.e.

    =SUMPRODUCT(--(t2_tca=$B6),--(t2_pgroup="D"),(t2_acres))

    If you get #REF! that might be because you have #REF! errors in the referenced ranges....
    Yes that was the case. I have spoken with our DBA about trimming varchar fields and using COALESCE to eliminate nulls from data. Actually, I resolved the issue by creating a function that concatenated the necessary data from the three columns. This created a single column eliminating the need for multiple criteria in SUMIF/SUMPRODUCT and the trimming/null value issues.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIF with multiple criteria, text and numerical
    By Radman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2008, 12:23 PM
  2. sumif with multiple criteria - not sure what functions to use
    By schueyisking in forum Excel General
    Replies: 0
    Last Post: 08-26-2008, 10:37 AM
  3. SUMIF function for multiple sheets
    By OrionGr8Hunter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2008, 08:38 AM
  4. using multiple criteria with sumif
    By Jerhansen277 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2007, 06:29 PM
  5. Multiple criteria use in SUMIF
    By chamdan in forum Excel General
    Replies: 8
    Last Post: 07-09-2007, 05:44 PM

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