count only cells if there is an even number in a seperate cell

  • I have a list of customers that have a number attached to them. Several customers I want to exclude from all the information I am gather and so I have given the a number like 103.4, so that they fall in line in the right place but I don't want to count their data. How do I add the sum of a column of information except where the row whose cell in column D is not whole?
    My formula currently looks like this.
    =SUM(Y13:Y123)-(Y84+Y85+Y86+Y102)
    It is simple but as I organize and rotate the data with new customers I am constantly going in and changing the subtraction because they are moving around.


    Thank you,
    Cya

  • Re: count only cells if there is an even number in a seperate cell


    I have attached a dummy file, basically the same layout just the data is not correct. The customer number is in column D and the cell I working on is N10, then I would fill right from there.


    Thank you,
    cya

  • Re: count only cells if there is an even number in a seperate cell


    I guess that could work. I was hoping to be able to pass this on to a couple of friends and I was trying to make it as simple for them as possible. I think this will work though.
    Thank you for the help.
    Cya

  • Re: count only cells if there is an even number in a seperate cell


    Hi there.


    You said you have inserted numbers “Like” 103.4 and from your file, the rows you don’t want to include have been marked with unique numbers eg 105.3, 103.3.
    Is it necessary to mark each unwanted row with a unique number? For instance, if you could mark all the unwanted numbers with say an “x”, then you could use something like


    SUMIF(D:D,”<>x”,E:E), depending on your ranges.


    If you must have unique numbers then you can use this array formula (Press CTRL+SHIFT+ENTER to enter the formula)


    =SUMPRODUCT(IF(MOD($D$14:$D$21,1)=0,1,0),E14:E21),

    Where D is the column with the identifier in, and E is the range you want to sum.

    Just to explain, the formula creates an array of 1s and 0s, depending on whether the number you have chosen to include or exclude your row is a whole number. If it is not a whole number, eg 103.4, the array returns zero. This array is then multiplied by another array (the range you are actually summing). Zero multiplied by anything is zero, leaving just the numbers you need.

  • Re: count only cells if there is an even number in a seperate cell


    Actually I need to keep them in order with the numbers so using the 102.3 or something like that allows me to keep them organized and with the formula you gave me it works out perfect! Thank you very much converteds.


    Can you help me with my other questions on the other threads?

Participate now!

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