Forum Discussion

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.

sampleSumifs.xlsx11 KB

Formulas and Functions

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%))

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%))

littlevillage
Iron Contributor
Dec 25, 2022
Lorenzo
Thank you for your response.
In your formula, I just slightly change as below:

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

It's worked correctly.
- Lorenzo
  Silver Contributor
  Dec 26, 2022
  littlevillage
  
  Makes sense re. what you ultimately expect to acheive
  Note PeterBartholomew1's SUMIFS approach that you can adapt to Excel 2021:
  =SUM(SUMIFS(Table1[unit], Table1[ID], G17, Table1[%percen], {"<99%",""}))

Resources