Countifs with a ranged area and multiple criteria

[davekent10]

Hi,

Been looking through this form and can find a solution to this, so hoping that someone might have one for me.

I'm using a COUNTIFS statement with multiple criteria. Use 2 sheets, 1st is call "Master" which has all the data, second is "Reports", which has all the formulas.

I'm trying to use the following formula:
=(COUNTIFS(Master!$E$2:$E$5000,C$1,Master!$U$2:$U$5000,$A$1,Master!V2:AV5000,$A$2,Master!$S$2:$S$5000,$B2))

Everything is straight forward except this part Master!V2:AV5000,$A$2
The value for Cell A2 is random value, that appears in columns on the Master sheet V through AV

The current value for A2 is equal to the values in Master!AG column, so when I change the formula to
=(COUNTIFS(Master!$E$2:$E$5000,C$1,Master!$U$2:$U$5000,$A$1,Master!AG2:AG5000,$A$2,Master!$S$2:$S$5000,$B2))

It returns the correct count. I would love to keep this, but since A2 changes each week, it's either I have to change the formula each week or go crazy.

I've tried defining a name for the range of Master!AG2:AG5000 with no luck

And advice or recommendations is welcome, I have no problem changing the entire formula as long as I get the correct output.

If more information is required, please let me know!

Thank you.

 

[Aladin Akyurek]

Is there a specific header in Master!V21:AV1 under which A2 occurs?

 

[davekent10]

Yes, each heading is the same as the value of each column, which is fixed and will never change. Headings for V, W, and Y in bold.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Communication[/TD]
[TD]Line of Fire[/TD]
[TD]Balance/Traction/Grip[/TD]
[/TR]
[TR]
[TD]Communication[/TD]
[TD]Line of Fire[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Communication[/TD]
[TD][/TD]
[TD]Balance/Traction/Grip[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Yes, each heading is the same as the value of each column, which is fixed and will never change. Headings for V, W, and Y in bold.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Communication
[/TD]
[TD]Line of Fire
[/TD]
[TD]Balance/Traction/Grip
[/TD]
[/TR]
[TR]
[TD]Communication
[/TD]
[TD]Line of Fire
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Communication
[/TD]
[TD][/TD]
[TD]Balance/Traction/Grip
[/TD]
[/TR]
</TBODY>[/TABLE]

Try...

=COUNTIFS(
  Master!$E$2:$E$5000,C$1,
  Master!$U$2:$U$5000,$A$1,
  INDEX(Master!V2:AV5000,0,MATCH($A$2,Master!V1:AV1,0)),$A$2,
  Master!$S$2:$S$5000,$B2)

 

[davekent10]

Aladin...you sir, are a genius!

Thank you so much!!!

 

[Aladin Akyurek]

Aladin...you sir, are a genius!

Thank you so much!!!

You are welcome.