Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sumif Blank in Excel Tables

0

Have a table want to sum the values in one column if the corrosponding value in an adjcent column is not blank. So, Column B has a lest of  deposits, Column D has check if the deposit is cleared. I want to sum all of the values in B that are not checked.

=SUMIF([Cleared]," ",[Payment]) doesn't work

Answer
Discuss

Answers

0
Selected Answer

To sum cells that DO NOT have blank values in the next column, you can do this:

=SUMIF(A1:A3,"<>"&"",B1:B3)

To sum cells that have blank values, do this:

=SUMIF(A1:A3,"",B1:B3)

Note that all quotation marks used in these formulas are double quotation marks, as opposed to single quotes or apostrophes.

Discuss

Discussion

When I was  using the SUMIF with a table I was selecting the the colunn heading and not the entire column.

So  the correct answer is
=SUMIF([Cleared],"",[Payment]) Sum all values in the Payment column which has nothing, or blank,  in the corrospoing Cleared column.

=SUMIF(Table3[[#Headers],[Cleared]],"",Table3[[#Headers],[Payment]]) does not work. 

The command posted in the question =SUMIF([Cleared]," ",[Payment]) will sum every value in the payment column with a corrosponding space in the cleared column.
Randino (rep: 2) Aug 9, '18 at 12:58 pm
I'm a little confused, did my solution work? I used sample range references, but you just have to change them to work for your worksheet. If my solution still doesn't work, just edit your question and upload a sample file and I can take a look at it.
don (rep: 1989) Aug 9, '18 at 1:01 pm
Randino's issue was with structured referencing - until it wasn't. Don, your answer didn't address structured referencing and therefore didn't obviously help. However, Randino has found the mistake himself and explained it very well. The error in his formla wasn't with the referencing but with the blank space between the quotation marks. " " instead of "". Once he found that mistake his structured references work as well as your unstructured ones.
Variatus (rep: 4889) Aug 9, '18 at 8:11 pm
That's why I was confused, based on his initial formula, in the question, he just removed the space and didn't change references, which is basically my second formula.
don (rep: 1989) Aug 10, '18 at 2:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login