Combining two criteria's for a countif Formula

[searchingforhelp]

Hi,

I am trying to count the number of instances when column A is greater or equal to 16 and has the word juice in a deferent column. The second part of the formula is not connecting with the first. Can anyone suggest how I can connect the two?

=Countifs(A2:A10,">=16")+Countifs(J2:J10,"Juice"))

Thank you for your help
Ernie

 

[Joe4]

With COUNTIFS, you put both criteria in the same formula.
If you do two formulas, you will get two separate counts that will be added together.\

Try:

=Countifs(A2:A10,">=16",J2:J10,"Juice")

See here for more explanation and details: MS Excel: How to use the COUNTIFS Function (WS)

 

[vmjan02]

try this

=COUNTIFS(A2:A10,">=16")+COUNTIFS(J2:J10,"Juice")

 

[Joe4]

try this

=COUNTIFS(A2:A10,">=16")+COUNTIFS(J2:J10,"Juice")

That won't work. That will count each condition separately, and add up the results. It is sort of like an incomplete "OR" (because you would also need to back out the the ones where both conditions exist on the same row to avoid double-counting), when they asked for an "AND".

Note the requirement:
I am trying to count the number of instances when column A is greater or equal to 16 and has the word juice in a deferent column.

 

[vmjan02]

@Joe4 I think that is what is the requirement of @searchingforhelp is
if count >=16 and Juice and result is to sum both the counts.

let's wait for @searchingforhelp reply.

or he can use the =Countifs(A2:A10,">=16",J2:J10,"Juice") -- this will consider both and give a single count.

Thanks.

 

[Joe4]

@Joe4 I think that is what is the requirement of @searchingforhelp is
if count >=16 and Juice and result is to sum both the counts.

It looks like they marked a solution to this problem already, but if they wanted a count of all rows where either of those conditions are true, then they would need to amend your formula like this:

=COUNTIFS(A2:A10,">=16")+COUNTIFS(J2:J10,"Juice")-Countifs(A2:A10,">=16",J2:J10,"Juice")

Otherwise, you will double-count rows where both conditions are true (and you could even conceivably end up with a count that is greater than your number of rows)!

 

[searchingforhelp]

Hi team, thank you for this. So to answer the question, I needed help to find what is greater then 16 in one column and if Juice is associated with it in a second column, count it.

Example 4-27-2021.xlsx
	A	B	C	D
1	Number	Name		There are three in total that fit the criteria
2	1	Banana
3	2	Juice
4	26	Juice
5	17	Apple
6	50	Juice
7	30	Banana
8	26	Juice
Sheet1

Hope this helps