sumif

[mjoyce1994]

i'm trying to do a sumif

i tried attaching a photo but not sure it worked and don't want to attach my excel at the moment

sumif(I:I,"1",(e119+J119))


The part in red is where I think i'm messing up. What I'm trying to say here is sumif the result in column I is "1" then sum these two cells.

 

[Fluff]

Hi & welcome to MrExcel.
What exactly do you mean by column I is "1"?

 

[Joe4]

Are you looking at the whole column of I, and then just summing columns E and J in row 119, or do you mean to check each row, and if column I in that row is 1, then add columns E and J?
If so, then you would use a formula like:

=IF(I119=1,E119+J119,0)

(is I is entered as a number)
or

=IF(I119="1",E119+J119,0)

if column I is entered as text.

 

[mjoyce1994]

Are you looking at the whole column of I, and then just summing columns E and J in row 119, or do you mean to check each row, and if column I in that row is 1, then add columns E and J?
If so, then you would use a formula like:

=IF(I119=1,E119+J119,0)

(is I is entered as a number)
or

=IF(I119="1",E119+J119,0)

if column I is entered as text.

The second one worked exactly as i wanted it to!!

 

[mjoyce1994]

Another question... the second if statement worked, btw! Lets say I wanted to not see #N/A's and wanted to see "-" instead, how would I add that to the formula?

 

[Joe4]

In what situation are you getting #N/A's?
There must be an error or some unexpected value in columns I, E, or J.
Which one is it, and what is in that cell?

 

[mjoyce1994]

It's due to a vlookup from another column.


For learning purposes, just wondering how to make it "-" instead of "n/a"

 

[Joe4]

It's due to a vlookup from another column.

But which column has this VLOOKUP? I, E or J?
If you would like me to amend the formula, I need to know which part (column reference) needs to be amended!