using countsif to count items from rows if certain conditions in other columns match

Question

I'm trying to count instances where a value is present in column D only if certain values are present in either E,F, or G. Specifically, if an agent's extension is present in the column and there is a number 2 in any of the three columns in the row after it, it should count it. I started with this:

=COUNTIFS(D2:D392,"9123",E2:E392,"2",F2:F392,"2",G2:G392,"2")

But that only counts it if a 2 is the value for all three of the those columns in that row. If the value of one of the columns differs, I still want to include it in the count if a 2 is present in any of the others.

I tried this:

=COUNTIFS(D2:D392,"9124",E2:G391,"2")

With the hope that it would compare the entirety of the columns/rows from E to G, and if they have a 2 and it matches the extension in question it will count it. Instead it returns the #VALUE! error.

Any suggestions for what I might be doing wrong?

barry houdini · Accepted Answer · 2017-04-07 19:24:57Z

1

Try using SUMPRODUCT, e.g.

=SUMPRODUCT((D2:D392=9123)*((E2:E392=2)+(F2:F392=2)+(G2:G392=2)>0))

I didn't use quotes around 9123 or 2. SUMPRODUCT is more fussy than COUNTIFS - you only need the quotes if those values are stored as TEXT, otherwise it should work as written

answered Apr 7, 2017 at 19:24

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

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

3 Comments

Paul Randall Over a year ago

I think that did the trick. If I'm understanding the formula right, it takes the number of times the extension is found in column D and multiplies it by the number of times the number 2 is found in any of the other three columns?

barry houdini Over a year ago

Almost - it won't count any row more than once even if there are 2 or 3 twos. Is it possible to have more than one 2 on the same row?

Paul Randall Over a year ago

Yes it is, and yes it should not count that row more than once. This solution works perfectly. Thanks for the help!

Collectives™ on Stack Overflow

using countsif to count items from rows if certain conditions in other columns match

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related