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


    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.

Participate now!

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

Register Yourself Login