Using a blank cell as a criterion in a SUMIF/S

**PJCIII** · 11-29-2007, 03:08 PM

I am trying to use SUMIFS in 2007 to replace an array function I used in older versions to a sum based on multiple criteria. One criterion has blanks as a legitimate entry and when I use SUMIFS or even just SUMIF and try to use the blank cell as the criterion it comes back as zero sum. If I check for equality between my formula reference cell (blank) and a blank cell in the criterion column, it comes back as TRUE (=A5=C27 returns TRUE).

If I use the same criteria but du it as a SUM array function it recognizes the blanks and returns the proper sum.

Is there a way to get SUMIF/S to recognize blanks as criterion?
Thanks,
Phil

**NBVC** · 11-29-2007, 04:44 PM

You mean either of these...

To some values in B where A has blanks... =SUMIF(A1:A6,"=",B1:B6)

To some values in B where A not blank... =SUMIF(A1:A6,"<>",B1:B6)

**NBVC** · 11-29-2007, 04:48 PM

or maybe:

=SUMIF(A1:A6,""&G1,B1:B6)

where G1 is a cell that can be blank or may contain a text string to search for

**jk007** · 08-11-2014, 11:07 PM

I got solved same problem. thanks.

**kkuryliw** · 10-09-2014, 10:48 AM

jk007,

What did you use to get this to work? I've tried the method described above and am having difficulties.

I have a SUMIFS that pulls from a field that consists of a data validation box, where values are listed that I can chose to filter the information by. I want to have an additional choice to pull all information including values that are blank.

As an example, sample set of values are:

<> - pulls all values, ignoring blanks
101
1810
1950
2000
blanks

etc. If I want just the 101, it gives me the information for that type. Likewise, <> gives me all of the listed non-zero values. I cannot for the life of me figure out how to grab all information regardless of what is in that field. I've tried <>&"", <>&=, and other variations thereof, all to no avail.

Thoughts? Thanks for the help.

**protonLeah** · 10-09-2014, 02:36 PM

kkuryliw,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.