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

#### MegWump

##### New Member
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?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Asala42

##### Well-known Member
=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

##### New Member
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

##### Well-known Member
There's a few ways.

If it's just the 5 columns you can brute force it:
Code:
``=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:
Code:
``{=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

##### New Member

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

##### New Member
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

##### Well-known Member
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

Replies
1
Views
20
Replies
9
Views
42
Replies
0
Views
60
Replies
3
Views
48
Replies
1
Views
33