sum based on conditions

[Pailong]

Hi all,

Sorry I'm fairly new to excel and not very good at formulas.

I have two columns. Column A has a list of three data sets, for example, SOY, RICE, BEAN. Column B has a list of names, for example John, Mark, David.

I want to use a formula that will scan both columns and, based on column B, find their choices in column A and add it up to a total. For example, I want to find all the instances where John has chosen RICE and get the formula to add that up for me automatically, so the result I get is something like John = 56 RICE.

Is it a SUM formula with either CountIF or V or H Lookup? I can't figure out how to do this.

Best,

Pailong.

[Raphaelp]

Try this:

=COUNTIFS(B:B,"JOHN",A:A,"RICE")

And then change it up for different people, foods, etc.

[AliGW]

Welcome to the forums!

The function you need to look at is COUNTIFS:

=COUNTIFS(B:B,"John",A:A,"Rice")

[Pailong]

Ah of course! It was CountIFS! It worked! Thanks very much.

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Pailong]

I do have one more question:

What if I needed my count to be dependent on several criteria in column A?

For example, if I wanted excel to count how many instances John (column B) has chosen RICE, BEAN (column A) i.e. more than just one choice in column A, how would I do that? Would it be CountIFS again?

[Tony Valko]

Maybe something like this...

=SUM(COUNTIFS(B:B,"John",A:A,{"Rice","Bean"}))

[Pailong]

Thanks very much!

[Tony Valko]

You're welcome!