Formula for doing a sum if a previous cell is of a particular option from a pick list

[MegWump]

I'm hoping this is really basic - I'm having a moment!

This is what my issue is:

Cell a1 could have values of Jobs, Tickets, Items or Packets.

The rest of the column is filled with values of 0, 0.5 or 1 (no blanks).

What formula do I need that gives me a total of the values if a1 is Jobs?

Can I do a sum within an if statement or similar?

 

[Asala42]

=IF(A1="Jobs",SUM(A2:A1000),"")

replace the "" at the end with what you want the formula to output when A1 is not equal to "Jobs".

 

[MegWump]

Duh! I forgot the "" around the word. Thank you.

Follow up question:I have the same column set up in columns b, c, d and e. I want to produce a single value which shows me the total values in all columns where Jobs is the heading - I thought I could do multiple IF statements and sum them, but I don't think I've got the layout right as it's giving me a #VALUE error.

 

[Asala42]

There's a few ways.

If it's just the 5 columns you can brute force it:

=IF(A1="Jobs",SUM(A2:A1000),"")+IF(B1="Jobs",SUM(B2:B1000),"")+IF(C1="Jobs",SUM(C2:C1000),"")+IF(D1="Jobs",SUM(D2:D1000),"")+IF(E1="Jobs",SUM(E2:E1000),"")

You could also use an array formula:

{=SUM(IF($A$1:$F$1="Jobs",A2:E1000))}

Don't type the brackets - to get them after writing the formula hit Ctrl+Shift+Enter.

 

[MegWump]

Thanks again. I think I'll have to go with the first option as I only want it to count alternate columns (I will have a separate count of the other columns).

 

[MegWump]

Ok, so the brute force option is giving me a value error - I think because it's trying to sum on text (Jobs) and then the values in the columns. I'll have a think about another layout that might make this easier, unless anyone has any other suggestions.

 

[Asala42]

Actually the SUM function usually just ignores text. Does the data range you are summing actually contain a #Value error? That will get passed to the total