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%))
HansVogelaar's avatar
HansVogelaar
MVP
Dec 22, 2022

littlevillage 

Excel doesn't include the two rows with an empty percentage in the result. If you enter 0% in D19 and D20, the formula will return 1 in H17 and H18.

littlevillage's avatar
littlevillage
Iron Contributor
Dec 22, 2022

HansVogelaar 

That two rows, I copied from a Pivot Table as below.

 

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Dec 23, 2022
    I have not tried myself but if you write "...,Table1[%percen]*1,..." all may be well.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 23, 2022

      ecovonrein 

      You cannot do that - functions such as SUMIFS expect a range, not an expression,

Resources