# sum if no blanks in matching cells

• how do I sum a column of numbers only if the column has a number for every item?
If it does not have a number for every item then it would not sum the column up.

Example

...C ..........D ........E
Item 1 ...\$1.00 ...\$1.00
Item 2 ...\$2.25 ...Blank
Item 3 ...\$1.25 ...\$1.25
Empty ....Blank ....Blank
Empty ....Blank ....Blank
Empty ....Blank ....Blank
Empty ....Blank ....Blank

Total ....\$4.50 .....Blank

• Re: sum if no blanks in matching cells

=IF(AND(ISNUMBER(D1),ISNUMBER(E1)),SUM(D1:E1),"")

Copy this down your list in column f. It will populate the sum when d and e are numbers, when they aren't, it will leave it blank.

I'm assuming this is what you're looking for.

• Re: sum if no blanks in matching cells

=if(sumproduct(--(isnumber(d2:d4)))=rows(d2:d4),sum(d2:d4),"")

• Re: sum if no blanks in matching cells

Thank you Rowddawg for the formula.

I don't think that I have explained my situation correctly.

I am trying to total columns "D" and "E"

But I only want to total columns "D" and "E" if column "C" is not blank.

Please note that there may be several blank cells in columns "D" and "E" if "C" is blank.

Thanks

Matt

• Re: sum if no blanks in matching cells

Robert your formula almost worked but because there are also blank cells between the items and the total it returned a blank cell.

Thanks

Matt

• Re: sum if no blanks in matching cells

Can you post a workbook?
Column C in this case has values and the result is as expected.
Are your cell literlally "BLANK" or just empty?

• Re: sum if no blanks in matching cells

Robert,

how do I post a workbook?

In column C the cells are empty or in other words there is nothing in them. The Cells in column C is where I enter the item number that I am looking for.
In Column D there is a Vlookup formula to another sheet that shows that suppliers pricing for that item number. For example the cell in D3: =IFERROR((VLOOKUP(C3,Supplier1!C:G,5,FALSE)*B3),"") B3 is quantity
In Column E it is the same as Column D but just for a different supplier price list. For example the cell in E3: =IFERROR((VLOOKUP(C3,Supplier2!G:M,7,FALSE)*B3),"")

So all of the "Blank" cells would have a formula in them except the cells in Column C. I've changed them to "Empty" in my example.
The reason that columns D and E would be blank is because 1) there is no item number entered in column C or 2) there is no matching price for that item number in the suppliers price sheet.

Hope this helps. Sometimes it is hard to explain these.

Thanks

• Re: sum if no blanks in matching cells

Without seeing the workbook, if you changed the end of the vlookups to 0's instead of blanks ""'s, and hid 0's in settings, I'm thinking Robert's formula would work.

• Re: sum if no blanks in matching cells

Any time you insert ""'s into a cell, it no longer is viewed as having a number, which is why excel isn't giving you the result you want I think... From thereon out, you'll have to get excel to see it as one if you somehow revert it back to one, usually by using value(cell)...

By using 0's instead of the blank it keeps this problem from showing up to begin with.

Hope that helps.

• Re: sum if no blanks in matching cells

I got it to work.
I had to change the formula in D & E cells to =IF(C3="",0,IFERROR((VLOOKUP(C3,Supplier1!C:G,5,FALSE)*B3),""))
I changed the cell formatting to not show zeros so that I could see the prices better
I used Robert Mika's formula =IF(SUMPRODUCT(--(ISNUMBER(D3:D25)))=ROWS(D3:D25),SUM(D3:D25),"")

This enabled me to see the total for only the suppliers that had pricing for all of the item numbers and for the suppliers that did not have a price for all of the item numbers the total is blank.

Thanks to Robert Mika and Rowddawg

Matt

• Re: sum if no blanks in matching cells

Glad it worked out. I used to use ""'s a ton and had to work around it to create sums/etc until I read (on this site) that it was a poor practice and why. Actually helps keep things significantly cleaner in my opinion to just hide the 0's... Worse problem for me though was nesting multiple vlookups in an if statement (makes excel work twice as hard... once to figure out if it is needed or not, then again once it realized it is needed...) but that's another matter...

As for the ;'s, I know they are separators between the different number types (positive and negative, for instance) and shows how excel treats all, but specifically I can't tell you. Someone else here I'm sure knows why.

• Re: sum if no blanks in matching cells

To post a workbook go into advance option (below the table where you are writting) and then you have an icon to attached your workbook.

• Re: sum if no blanks in matching cells

Thanks

• Re: sum if no blanks in matching cells

Thanks

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!