COUNTIFS - count if value exists in a second range

[yrpsoa]

Hi,

Hoping someone can help - I'm trying to add an additional condition to the below formula to check whether the value exists in another (dynamic) range, however the best I seem to be able to get is that it compares value 1 with value 1 and value 2 with value 2.

For clarity, "main_data" is the source data being analysed and "company" is the list being compared against. The majority of the below works perfectly fine and can be ignored - it's the last comparison (INDEX(main_data,,9),company) I'm having trouble with.

  =COUNTIFS(INDEX(main_data,,4),">=" &'Dashboard - Gender'!$Y$63,INDEX(main_data,,4),"<=" &$AD$63,INDEX(main_data,,28),AW$172,INDEX(main_data,,15),"Employee",INDEX(main_data,,9),company)

The formula for the dynamic range of "company" is

  ='Dashboard - Gender'!$AU$182:INDEX('Dashboard - Gender'!$AU$182:$AU$218,COUNTA('Dashboard - Gender'!$AU$182:$AU$218))

Any help would be MUCH appreciated!

Thanks in advance!

 

[steve the fish]

Wrap the whole formula in a SUMPRODUCT.

 

[yrpsoa]

Wrap the whole formula in a SUMPRODUCT.

So simple when you know how! Thanks very much!

 

[yrpsoa]

Wrap the whole formula in a SUMPRODUCT.

Follow up question if I may: would you know why it reduces the result total when I add multiple instances of this, even when the lookup range contains all available values for the respective source data column?

Without these, the total is 224. When I add the first, it stays at 224. When I add the second it drops to 30, and the third takes it to 0.

I've double checked, and the values in the lookups contain ALL the possible values in the source.

 =SUMPRODUCT(COUNTIFS(INDEX(main_data,,4),">=" &'Dashboard - Gender'!$Y$63,INDEX(main_data,,4),"<=" &$AD$63,INDEX(main_data,,28),AW$172,INDEX(main_data,,15),"Employee",INDEX(main_data,,9),lu_company,INDEX(main_data,,10),lu_BU,INDEX(main_data,,21),lu_location))

Thanks again,
Adam

 

[steve the fish]

Not sure what you mean by multiple instances of this.

 

[yrpsoa]

Not sure what you mean by multiple instances of this.

Thanks for the quick response.

If you look at the end of the formula, I've tried to combine a couple of these:

INDEX(main_data,,9),lu_company
INDEX(main_data,,10),lu_BU
INDEX(main_data,,21),lu_location

The issue seems to be that, because the lengths of the lookup ranges are different, it pads the remainder with 0's, which when multiplied with valid values, cancels them out.

Attached are some screenshots to demonstrate, using one comparison at a time, and then combining two.

Hope this clarifies the situation?

Thanks again,

 

[steve the fish]

In that instance you will need to create more than one countifs and sum them together.

 

[yrpsoa]

In that instance you will need to create more than one countifs and sum them together.

Interesting - but I would like it to return the number only when the row in the source data meets all conditions. By creating separate countifs, would this not take any that meet criteria 1, and add any that meet criteria 2 - meaning it would grow the result, rather than narrow it down, and would count a row three times if it met 3 conditions?

I really appreciate your help with this.

Thanks,
Adam

 

[steve the fish]

Are company, bu and location named ranges single cells then?

 

[yrpsoa]

No - BU is a list of 3 and location is approx. 40.

Basically, I'm trying to use these lists like slicers, so you can filter* them and change the result of the countif. If you had multiple slicers on a pivot table, selecting items from each would find only results that meet all conditions from all slicers. This is what I'm trying to achieve. (Yes, I've considered using a pivot table, but that wasn't possible unfortunately)

* I know a formula ignores filters, but I've already got this covered. For simplicities sake, I'm filtering the list, and the named range is accounting for this.

Thanks again,