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

MegWump

New Member
Joined
Feb 20, 2009
Messages
33
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
Joined
Feb 26, 2002
Messages
2,318
=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
Joined
Feb 20, 2009
Messages
33
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
Joined
Feb 26, 2002
Messages
2,318
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
Joined
Feb 20, 2009
Messages
33

ADVERTISEMENT

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
Joined
Feb 20, 2009
Messages
33
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
Joined
Feb 26, 2002
Messages
2,318
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top