Sum when numbers are indicated by "X" [SOLVED]

  • Hi all,

    Can anyone help me with a formula or solution to get the sum of a range of numbers which are indicated by an X?
    I have attached a file so u guys may have a better idea.

    Thanks :puzzled:

  • one possible solution:

    assuming that the only thing that can turn up in the "date" area is a X, insert another column that checks for whether a cross has been entered. something like:


    ...array entered using control + shift + enter, not just enter, could be used to perform the check. you can then use a simple sumif() to get the result:


    obviously, you could hide the column that contatined the first calculation (AJ in this example)


  • As Paddy indicated, an array formula is the way to go:
    enter this into a cell, but use CTRL-SHIFT-ENTER when you enter it.

    Otherwise you can use =sumif to get the total one column at a time: ie to get total in column :
    (just use enter - this is not an array formula)

  • Thank you !! i used both methods at different columns and cells and it worked fine for me.

    Thanks for the prompt reply and help! :coolwink:

Participate now!

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