Rewrite COUNTIFS for dynamic array criteria

Question

Example Data and Expected Results

I want to get the count of names with diagnosis 2 and drug A or any combination of the two from the data set, but the criteria column is a mix of different criteria. I know that what I basically need to do is a COUNTIFS where one of the criteria range is an array, but I'm not sure how to get the initial array of Names.

For Cell F6:

=COUNTIFS(Criteria,"Drug",Value,"A",Names,ArrayOfNamesWithDiagnosis2)

Where Criteria is A2:A12, Value is B2:B12,Names is C2:C12, and ArrayOfNamesWithDiagnosis2 is the list of the names that have diagnosis 2.

Icron · Accepted Answer · 2025-09-18 15:47:51Z

1

For cell F6, I found this formula to work:

=SUM(COUNTIFS(C2:C12,FILTER(C2:C12,A2:A12="Diagnosis",""),B2:B12,2))

Using the named ranges described above:

=SUM(COUNTIFS(Names,FILTER(Names,Criteria="Diagnosis",""),Value,2))

edited Sep 18 at 15:47

answered Sep 16 at 22:44

Icron

296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user22566114 · Accepted Answer · 2025-09-21 15:17:53Z

0

This is the formula in cell G9. The formula is filled up and confirmed with ctrl+shift+enter because i work with legacy Excel 2013. Not 100 % sure if this does what you want but that's how i understand the task.

=SUM(COUNTIFS($B$2:$B$18,F9,$C$2:$C$18,IF($B$2:$B$18=E9,$C$2:$C$18)))

I've made a small change to your database in cell B4 is now "A" instead of "B". I wanted to know if the formula returns the intended result for diagnosis 1 and "John".

edited Sep 21 at 15:17

answered Sep 21 at 15:12

user22566114

5464 silver badges6 bronze badges

Collectives™ on Stack Overflow

Rewrite COUNTIFS for dynamic array criteria

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related