Sum based on multiple selection criteria

1. Sum based on multiple selection criteria

I would like to sum based on multiple selection criteria that are look-ups to another tab.

For example, I want to calculate the sum of column c (in sheet1) where column A matches a value in column A in sheet2, and where column B = "yes". The result in the below example would be 10.

sheet1:
x yes 1
x yes 2
x no 3
x no 4
y yes 5
y no 6
z yes 7
z no 8

sheet2:
x
z

Thanks in advance for any help that you can provide!

2. Re: Sum based on multiple selection criteria

One way...

Data Range
 A B C D E F 1 x yes 1 x 10 2 x yes 2 z 3 x no 3 Yes 4 x no 4 5 y yes 5 6 y no 6 7 z yes 7 8 z no 8 9 ------ ------ ------ ------ ------ ------

This formula entered in F1:

=SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3))

3. Re: Sum based on multiple selection criteria

Hi mcnallyb and welcome to the forum,

See if the attached does what you want. You should also update your profile to show what version of Excel you are using as the early versions didn't have the SumIfS () functions.

4. Re: Sum based on multiple selection criteria

=sumifs(Sheet1!C1:C:100,Sheet1!A1:A100,Sheet2!A1,Sheet1!B1:B100,"yes)

5. Re: Sum based on multiple selection criteria

Or this.....

Assuming that your sample data on Sheet1 is in the range A1:C8 and on Sheet2 is in the range A1:A2, then

``Please Login or Register  to view this content.``

6. Re: Sum based on multiple selection criteria

Hi,

Marvin, Tony and Soberguy all got different answers. All used different formulas. I guess this shows we aren't all experts. You should never trust an answer on this site unless you test it out for yourself. See all three formulas and answers in the attached.

sktneer just gave an answer that is different still, but at least it looks to be the correct number. (If you agree with MarvinP's answer)

7. Re: Sum based on multiple selection criteria

Marvin, I get invalid link when I click that.

8. Re: Sum based on multiple selection criteria

Thanks, but the data will be in different sheets. Just referencing another sheet using "sheet2!" doesn't seem to work.

9. Re: Sum based on multiple selection criteria

Try again as I was updating my workbook to include sktneer's answer too.

10. Re: Sum based on multiple selection criteria

My formula is the only one that returns the correct result!

The correct result is 10. These are the rows that meet the criteria: 1, 2 and 7.

11. Re: Sum based on multiple selection criteria

Thanks, Tony. But what is the syntax across sheets? I can't get it to work.

12. Re: Sum based on multiple selection criteria

In my exhibit I put everything on the same sheet to make it easier to see what's happening.

13. Re: Sum based on multiple selection criteria

Thanks, Tony, I did get the syntax correct. I appreciate your help!

14. Re: Sum based on multiple selection criteria

Originally Posted by mcnallyb2
Thanks, Tony. But what is the syntax across sheets? I can't get it to work.
You didn't mention that the formula needs to work across multiple sheets.

That changes things significantly!

What are the real sheet names?

15. Re: Sum based on multiple selection criteria

I thought by mentioning sheet1 and sheet2 in the example, it was clear. Sorry. Thanks again...I go it to work!

16. Re: Sum based on multiple selection criteria

Actually all the three formulas are returning the same result. Marvin changed the values on sheet2 to x,y instead of x,z as per the sample in post#1.

17. Re: Sum based on multiple selection criteria

Tony - my apologies, you did get the answer correct.

I thought the criteria on sheet2 was x, y and after rereading it, it was x, z (not y). I got the problem wrong, but the right answer for the wrong problem. Does that give me half credit?

Good thing I don't have this work as my day job...

18. Re: Sum based on multiple selection criteria

Apparently I need to work on interpreting what people want a bit better. I pictured it as either x or z, not both. It's still beyond me how everyone else arrived at that conclusion, even after re-reading the OP.

19. Re: Sum based on multiple selection criteria

Hey sktneer,

Thanks for fixing my sheet2 "typo" and making the y a z. Maybe I'll get a bigger monitor or new glasses before trying to answer more questions.

20. Re: Sum based on multiple selection criteria

OK, I think I misunderstood what you meant by "across sheets".

Sounds like you're good to go!

21. Re: Sum based on multiple selection criteria

Originally Posted by MarvinP
Hey sktneer,

Thanks for fixing my sheet2 "typo" and making the y a z. Maybe I'll get a bigger monitor or new glasses before trying to answer more questions.
Actually I was trying to fix my formula when Tony said that only his formula is returning the correct output then only I found that typo. We all got confused just because of that typo. lol

22. Re: Sum based on multiple selection criteria

Hi Tony,

Thanks again for your help. I'm trying to expand on the concept to have multiple criteria, each with a range of values. I just added "no" in E4, and want to add it to the selection criteria (sorry, I don't know how to add a table in the reply):

A B C D E F
1 x yes 1 x 10
2 x yes 2 z
3 x no 3 Yes
4 x no 4 No
5 y yes 5
6 y no 6
7 z yes 7
8 z no 8

Trying to expand the formula, I made it: =SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3:E4))
The value returns 11, but I would expect 25.

23. Re: Sum based on multiple selection criteria

Like this...

Data Range
 A B C D E F G 1 x yes 1 x 25 2 x yes 2 z 3 x no 3 Yes No 4 x no 4 5 y yes 5 6 y no 6 7 z yes 7 8 z no 8 9 ------ ------ ------ ------ ------ ------ ------

This formula entered in G1:

=SUMPRODUCT(SUMIFS(C1:C8,A1:A8,E1:E2,B1:B8,E3:F3))

Note how the criteria ranges are in opposite directions. This is a quirk of the SUMIFS function when using multiple arrays as criteria.

24. Re: Sum based on multiple selection criteria

Thank you! Again, I really appreciate your help!

25. Re: Sum based on multiple selection criteria

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)