Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Dec 22, 2022
Solved

SUMIFS not working with blanks.

Hi,  I try to write a formula:  =SUMIFS(Table1[unit],Table1[ID],G17,Table1[%percen],"<99%") Hope for your response, Thank you.      
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 23, 2022

    Hi littlevillage 

     

    If you run 2021 or 365, in H17:

    =SUM((Table1[ID]=G17)*(Table1[%percen]<99%))

    With other versions, to avoid validating the above as an array formula (Ctrl+Shift+Enter on Windows), in I17:

    =SUMPRODUCT(--(Table1[ID]=G17), --(Table1[%percen]<99%))
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 23, 2022

littlevillage 

I hadn't realised that SUMIFS did not recognise a blank cell as a 0 for the purposes of testing a numeric criterion.  Since it doesn't, the following tests both against the numeric "<99%" and equals ""

=LET(
    distinctID, UNIQUE(Table1[ID]),
    unitSums,   SUMIFS(Table1[unit], Table1[ID], distinctID, Table1[%person], {"<99%", ""}),
    unitSum,    BYROW(unitSums, Sumλ),
    HSTACK(distinctID, unitSum)
)

and then combines the result.  The first and last rows calculate the distinct IDs and finally, stacks them with the numeric results.  The SUMIFS returns two columns by lifting the criterion array.  These could be combined using MMULT but I have chosen to us BYROW and a lambda.ised version of the SUM function instead.

Sumλ
= LAMBDA(x, SUM(x))
littlevillage's avatar
littlevillage
Iron Contributor
Dec 27, 2022

PeterBartholomew1 

Thank you very much.

I'm not sure i can fully understand functions that you used, but i feel you try to combine different functions so i can keep my approach. That's sumifs.

Regards,

Tuan.

Resources