+ Reply to Thread
Results 1 to 4 of 4

Neither SUMIFS nor SUMPRODUCT deliver the required result

  1. #1
    Registered User
    Join Date
    12-10-2010
    Location
    Singapore
    MS-Off Ver
    MS Excel for Mac 2011
    Posts
    51

    Neither SUMIFS nor SUMPRODUCT deliver the required result

    The RANGE CRITERIA are in column B, C, D and E, rows 4-43.
    The CRITERIA also in column B, C, D and E in row 52,
    The SUM RANGE in column F, rows 4-43.
    =SUMIFS(F$4:F$43,$B$4:$B$43,$B52,
    $C$4:$C$43,$C52,
    $D$4:$D$43,$D52,
    $E$4:$E$43,$E52)

    The SUMIFS works like a charm when all CRITERIA (B52, C52, D52, E52) are correctly populated. If however (i) one or more of the CRITERIA is not populated or (ii) is populated with a CRITERIA that doesnít match with any of the fields in the respective RANGE CRITERIA, the result is zero.
    What I would like the formula to do is SUM, based on one, two, three or all four criteria.

    To no avail, Iíve also tried to get the required result a SUMPRODUCT formula:
    =SUMPRODUCT(($B$4:$B$43=$B52)*
    ($C$4:$C$43=$C52)*
    ($D$4:$D$43=$D52)*
    ($E$4:$E$43=$E52)
    *F$4:F$43)
    The SUMPRODUCT results are identical as with SUMIFS: works when all CRITERIA are correctly populated, donít deliver the required result when one, two, three or all four criteria arenít populated or doesnít match with any of the fields in the respective RANGE CRITERIA.

    Can anyone help me?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,341

    Re: Neither SUMIFS nor SUMPRODUCT deliver the required result

    Both SUMIFS and SUMPRODUCT require all criteria to be met for given row to sum this row
    you may try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,341

    Re: Neither SUMIFS nor SUMPRODUCT deliver the required result

    or more complicated if you want all criteria, which are not empty to be met at once, and criteria which are not populated in given row (52) to be treated as met:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-10-2010
    Location
    Singapore
    MS-Off Ver
    MS Excel for Mac 2011
    Posts
    51

    Re: Neither SUMIFS nor SUMPRODUCT deliver the required result

    Thanks Kaper!

+ 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. summing in excel using sumifs help required
    By yousaf125 in forum Excel General
    Replies: 6
    Last Post: 12-25-2015, 03:31 PM
  2. [SOLVED] SUMIFS - #VALUE and not a result
    By Vestlink in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2015, 04:54 AM
  3. [SOLVED] SUMIFS - sumif required but dependent on entry in 2 cells
    By karimel_romeo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2014, 01:51 PM
  4. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  5. Help Required with Sumif, Sumifs function
    By shantanuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2012, 08:54 AM
  6. [SOLVED] SUMIFS + condition on result
    By Davzx in forum Excel General
    Replies: 4
    Last Post: 06-28-2012, 12:59 PM
  7. Replies: 2
    Last Post: 10-20-2011, 05:41 AM

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