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%))
Lorenzo's avatar
Lorenzo
Silver Contributor
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%))

Resources