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?
 

Some videos you may like

Excel Facts

Add Bullets to Range
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
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,877
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top