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]

Essentially what you are asking for is combinations of COUNTIFS summed together. You can only do that twice in a single countifs. So you will have to have 3 countifs using your criterias but having the BU named range as a fixed value. If you send the entire formula ill try and slice it up. Also send the 3 values that are currently in BU named range.

 

[yrpsoa]

Essentially what you are asking for is combinations of COUNTIFS summed together. You can only do that twice in a single countifs. So you will have to have 3 countifs using your criterias but having the BU named range as a fixed value. If you send the entire formula ill try and slice it up. Also send the 3 values that are currently in BU named range.

Current formula is:

=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))

(currently evaluating to 0)

The 3 values in BU are GCF, RMU, SUN

Thank you!

 

[steve the fish]

So you could do this:

=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),TRANSPOSE(lu_company),INDEX(main_data,,10),"GCF",INDEX(main_data,,21),lu_location))*ISNUMBER(MATCH("GCF",lu_BU,0))

then do the same for RMU and SUN and sum the whole lot together. Ive used transpose because i assume your named ranges are all in the same orientation.

 

[yrpsoa]

I see what you mean! That's brilliant - thanks very much