Excel COUNTIFS criteria... I want to count all names in range1 (not a specific name), that meet specific criteria from range2. How do I do this?

Question

For reference, using COUNTIFS is required and should not be used in combination with other functions.

I have a table of unique customers and monthly sales amounts:

Customer	Jan	Feb	Mar
Bob	176953*	145689*	167459*
Joe	33489	35541	31997
Ann	24573	17654	120646*
Mary	78735	86795	84400

(over 100,000 marked with '*' for easy ref)

Using COUNTIFS, I need to count how many of my customers made sales >100,000 over a period of 3 months. The answer should be 2 (Bob, Ann).

But my problem is, if I use Criteria_range1 A2:A5 (names) ..what do I put as Criteria1? ...I'm not trying to find all the matching names (ie. all 'Bobs' in the list), they are all unique?

Criteria_range2 should be B2:D5, criteria2 >100000

And if I put the range of months as the first criteria, I'll return 4, the amount of times sales were over 100,000 ... which is not the solution I need.

I'm sure I'm missing something simple here, but since COUNTIFS is usually intended for specific criteria in multiple columns, I'm a little thrown off. (ie. all crit1:'fruits' in crit2:'good' condition...)

Thanks for any help.

EDIT: I am using Excel 365, for those who asked.

EDIT 2: Here is the exact question: (The table is actually larger, I only included the first few rows for simplicity's sake)

In cell H11 create a formula that counts the number of customers that had sales greater than 100,000 in every one of the three months. You are expected to use the COUNTIFS function in your formula to answer this question correctly.

The number of customers that had sales greater than $100,000 each of the 3 months: [Insert formula]

Just to be sure, "Ann" did not make over 100,000 in all these three months but still that counts. So you are specifically looking to count those people that made over 100,000 at least once? Can you let us know what version of Excel you are working with? — JvdV
Given your requirements, I guess this is homework or an assignment of some sort? — Rory
@JvdV I am using Excel 365, I have edited the post to include that info. — SteamCode666
My guess is that it can't be done with using just countifs. Can you post the entire assignment as it's written or verify that you can't use any other function. — sous2817

Rory Rory · Accepted Answer · 2021-03-09T16:00:00

The actual question is quite different from the original version you posted, and makes it much easier. It's just something like:

=COUNTIFS(B:B,">100000",C:C,">100000",D:D,">100000")

Excel COUNTIFS criteria... I want to count all names in range1 (not a specific name), that meet specific criteria from range2. How do I do this?

2 Answers