SumIFs, using a list as possible criteria...

Honestly, I would recommend having a seperate sumif for each value in C.
Then sum the results of those.

Why? Because I can't imagine not wanting to also know the result for each individual in C
Surely at some point you will be wanting to know "How many did Joe have"

Originally Posted by Jonmo1

Honestly, I would recommend having a seperate sumif for each value in C.
Then sum the results of those.

Why? Because I can't imagine not wanting to also know the result for each individual in C
Surely at some point you will be wanting to know "How many did Joe have"

We have a separate sumif for each of them already in another section, now we need them added.

Sktneer, worked perfectly!
Thanks

Originally Posted by sktneer

Try this.....

Please Login or Register  to view this content.

Works great thanks. Would you mind just explaining what this formula does? It works but I don't really know what it means.

Also, if I wanted to add more criteria to this, how would I do that?

Thanks again

Originally Posted by DeeRok

We have a separate sumif for each of them already in another section, now we need them added.

Then just sum those.

Creating a new formula like this is just duplicating work Excel has already done.

Originally Posted by Jonmo1

Then just sum those.

Creating a new formula like this is just duplicating work Excel has already done.

Then I'm back to square 1. How do I create a formula that tells excel to sum these values only if they are in the separate list?

Originally Posted by DeeRok

Then I'm back to square 1. How do I create a formula that tells excel to sum these values only if they are in the separate list?

I don't get it. If you have a separate list of staff on a separate page, say in A1:A10, then in B1 you'd have:
=Sumif(DataPage!B:B,DatePage!A:A,A1)

which you can then copy and paste down as far as necessary.

Originally Posted by soberguy

I don't get it. If you have a separate list of staff on a separate page, say in A1:A10, then in B1 you'd have:
=Sumif(DataPage!B:B,DatePage!A:A,A1)

which you can then copy and paste down as far as necessary.

Because all the staff are listed together so I need to cherry pick which ones I want added. I'm trying to add up certain divisions. I could go to page 1 and see everyone's totals. Then I want to summarize it based on the lists of people on a different page.

I'm not sure if I'm being clear or not, but this seems like the only way to do it.

OK, you said

Originally Posted by DeeRok

We have a separate sumif for each of them already in another section, now we need them added.

So the sample you posted, I put an individual sumif for each value from into column D
=SUMIF($A$2:$A$7,C2,$B$2:$B$7) Highlighted yellow in the attached book.
These yellow highlighted formulas represent what you said you already have somewhere else.

I am suggesting to just sum those
=SUM(D2:D4) Highlighted Green in the attached book.

EFDeeRok.xlsx

Originally Posted by DeeRok

I need to cherry pick which ones I want added.

OK, I see now. That had not been clear from previous posts.

See new version of attached file

EFDeeRokCherryPick.xlsx

I understand what you guys are saying, it just wouldn't work in my spreadsheet. Maybe it's because I oversimplified the data in the OP. Thank you all for the help though.

The cherry pick one is the right idea, but it adds an extra column I'd prefer not to have. That would be perfect though if you could do that without column E

Originally Posted by DeeRok

Because all the staff are listed together so I need to cherry pick which ones I want added. I'm trying to add up certain divisions. I could go to page 1 and see everyone's totals. Then I want to summarize it based on the lists of people on a different page.

I'm not sure if I'm being clear or not, but this seems like the only way to do it.

Well, I can certainly understand that, then. Though I think the solution of summing the SumIf formulas will still work. Let's just tweak it.

Perhaps you can have a list of staff and their corresponding division?

Name	Group
John	Group A
Steve	Group B
Sheryl	Group B
Mary	Group C
Larry	Group B

Then you'd have something like:

Group	Total
Group A
Group B
Group C

Then you just need a formula to go under the Total heading next to the Group, yeah?

Originally Posted by DeeRok

The cherry pick one is the right idea, but it adds an extra column I'd prefer not to have. That would be perfect though if you could do that without column E

Aren't you already 'adding' a column by creating the new list of your cherry picked names??

Besides, helper columns are NOT a bad thing.
XL gives you over 16 thousand of them, might as well use a couple.