# 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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### 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
186
Replies
1
Views
269
Replies
1
Views
248
Replies
1
Views
199
Replies
3
Views
801

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,484
Messages
5,831,934
Members
430,094
Latest member
soconfused365

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back