Sumif - can I use it

[Tim K]

Hi


I would like to sum a row of numbers ( say 4 numbers but in cells A4=5,C4=10,E4=15,G4=abs) but I don"t want to sum the cells in the row in between - say if B4 was 12 - or if there is text in any of my designated cell - in this case G4 has text.


I have been trying sumif and using "<>*" but I can't separate out the individual cells to add without putting in a range. I have been using =SUMIF(A4:G4,"<>*",A4:G4) and it works but adds the cells in between and the answers are wrong.


Thank you in advance if anyone can solve it for me - it would be much appreciated


Cheers

 

[kchiba]

Hi Tim,


If there is text in a cell the SUM() formula would ignore that, so I suggetst that you use =SUM(A4:G4) - B4, or alternatively use a helper row with an indicator for the cells you want to exclude and then use the SUMPRODUCT, or SUMIF formula.


regards


kanti

 

[Tim K]

kchiba,


Thanks for that quick reply. It is still working on the problem you helped me with last week - which I really appreciate.

They are big student results spreadsheets and I would love to be able to use a formula something like:


=SUMIF(A4:G4,"<>*,A4,C4,E4,G4) but that obviously doesn't work but you can see what I'm trying to do.


Cheers

 

[kchiba]

Hi Tim,


Try this, if there is text in any of your designated cells, it returns an error and the IF test wil make the result O, else it will sum up the required cells


=IF(ISERROR((A4*1+C4*1+E4*1+G4*1)),0,A4+B4+E4+G4)


kanti

 

[Tim K]

Hi again,


Thanks for the idea to try.

I tried it and it posted the result TRUE so I'm just not sure what is happening there.


Cheers

 

[kchiba]

Hi Tim,


There was a typo in the previous formula, below is the correct one.


Please ensure that you have the brackets and the commas in the correct places.


T have tested it and it returns the correct result. The formula is in cell H4


=IF(ISERROR((A4*1+C4*1+E4*1+G4*1)),0,A4+C4+E4+G4)


If you still have a problem, copy paste your formula from Excel, so that I can see it.


cheers

 

[Hui]

Whats wrong with

=SUM(A4,C4,E4,G4)

 

[Tim K]

Thanks again,


I do appreciate your time and efforts.


It works well but if a student is absent then it returns a zero whereas I would like it to total the other three cells.


ie A4=10,C4=20,E4=30,G4=40


Then the total column should be 100. But if G4=abs then the total column should be 60.

If both E4 and G4 is abs then the total should be 30


Cheers

 

[Hui]

Thats exactly what

=SUM(A4,C4,E4,G4)

Does ?

 

[Tim K]

Hui and kchiba,


That works a treat.


I was adding them individually and the text was not allowing an answer.


Cheers for taking the time to help me out.


I'm grateful for your help.

 

[kchiba]

Hui,


Looking at the original request, it says that he does not want a sum if there is text in any of his designated cells, that means if there is text in any one cell it should not SUM


"but I don"t want to sum the cells in the row in between - say if B4 was 12 - or if there is text in any of my designated cell - in this case G4 has text"


cheers