Counting Two Columns based on Criteria

[Excel_77]

Hi All,

I am working on spreadsheet 2.

I want to go into spreadsheet1 (tab1), search column A for "Yes" and column B for "George", if both criteria is met I want to return the number of entities, how is this done?

Thanks

 

[Joe4]

They are really doing the same thing, I just made mine a little more explicit, using:
"<>" & ""
instead of
"<>"

It is not necessary, but just makes it a little clearer to me to see what is going on. But the formula is a little shorter to do it your way.

Note that you do have a typo in your formula. All your arguments are separated by commas, but your last two are separated by a semi-colon.
I am guessing that maybe you are using a European version that uses semi-colons to separate arguments, and were manually changing it and just missed the last one.

 

[Excel_77]

They are really doing the same thing, I just made mine a little more explicit, using:
"<>" & ""
instead of
"<>"

It is not necessary, but just makes it a little clearer to me to see what is going on. But the formula is a little shorter to do it your way.

Note that you do have a typo in your formula. All your arguments are separated by commas, but your last two are separated by a semi-colon.
I am guessing that maybe you are using a European version that uses semi-colons to separate arguments, and were manually changing it and just missed the last one.

That worked really well, one final question if you don't mind?

Is it possible to extend this formula to add the corresponding values in columns D and E if could A does not equal Yes and Column B does not equal George?

 

[Caribeiro77]

Your correct about the semi-colon and commas, my excel is the European version, and i forgot to manually change that last 2 arguments....
Thanks...

They are really doing the same thing, I just made mine a little more explicit, using:
"<>" & ""
instead of
"<>"

It is not necessary, but just makes it a little clearer to me to see what is going on. But the formula is a little shorter to do it your way.

Note that you do have a typo in your formula. All your arguments are separated by commas, but your last two are separated by a semi-colon.
I am guessing that maybe you are using a European version that uses semi-colons to separate arguments, and were manually changing it and just missed the last one.

 

[Joe4]

Is it possible to extend this formula to add the corresponding values in columns D and E if could A does not equal Yes and Column B does not equal George?

You can keep adding arguments (conditions) to the COUNTIFS function (I believe you can do at least 32).
So it should just be more of the same.
Now that we have done a few for you, give this one a try yourself and see how you do. It will be a good test to see if you understand COUNTIFS.
If you are not clear, take a look at the link I provided earlier.
If you cannot get it to work, post the formula you came up with here, and we will help you clean it up.

 

[Excel_77]

You can keep adding arguments (conditions) to the COUNTIFS function (I believe you can do at least 32).
So it should just be more of the same.
Now that we have done a few for you, give this one a try yourself and see how you do. It will be a good test to see if you understand COUNTIFS.
If you are not clear, take a look at the link I provided earlier.
If you cannot get it to work, post the formula you came up with here, and we will help you clean it up.

The below is not working at all: If B= Yes and C= George I want to add the corresponding values in D and E together.

=COUNTIFS('Sheet1'!B:B,"Yes",'Sheet1'!C:C,"George",'Sheet1'!D:D+E:E)

 

[Joe4]

OK, I misunderstood your last question.
What you actually want is SUMIFS, which works the same way as COUNTIFS, but just has an extra column for the columns you want to sum.
See: https://www.techonthenet.com/excel/formulas/sumifs.php

Note that since you want to sum two columns, I think you will need two SUMIFS function that will be very similar. They are both checking the same conditions. One is just summing column D and one is just summing column E.
Then just add those two SUMIF formulas together to get your result.

 

[ZVI]

...Note that since you want to sum two columns, I think you will need two SUMIFS function that will be very similar. They are both checking the same conditions. One is just summing column D and one is just summing column E.
Then just add those two SUMIF formulas together to get your result.

Like this, for example: =SUMIFS(D:D,B:B,"Yes",C:C,"George")+SUMIFS(E:E,B:B,"Yes",C:C,"George")