index match or hlookup? between two dates multiple countifs

[Piers]

Morning all,

I have three criteria for my countifs formula 1. Must match "U", 2. count only the values "U" above or equal to the date in Main!F5 3. count only the values "U" below or equal to the date in Main!G5

ive been trying to work with index and match but I cannot for the life of me get it to work in any other format.

current formula is but either yields a 1 or N/a value - as in a true statement and not sure how to work it

=COUNTIFS(INDEX($K$5:$AAA$1000,,MATCH($I$7,INDEX($K8:$AAA8,1,),0)),">="&Main!$F5,INDEX($K$5:$AAA$1000,,MATCH($I$7,INDEX($K8:$AAA8,1,),0)),"<="&Main!$G5)

any help will be appreciated

 

[Piers]

Further information - i would like to be able to drag this down to count per row - the date variable of november is to stay the same but the row count by row is important - names have been hidden so this is per patient

 

[Fluff]

That image is useless as it is unreadable, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Piers]

im not allowed to download any add ons or programmes onto this computer as is a work security thing.

the best i can do is post redacted screenshots


sorry for being no help

 

[Piers]

im not allowed to download any add ons or programmes onto this computer as is a work security thing.

the best i can do is post redacted screenshots


sorry for being no help

 

[Fluff]

Unfortunately that image is still useless as it doesn't show the row numbers or the cells that you are referencing in the formula.
Can you please explain in words what you are trying to do?

 

[Piers]

In cell I8 i would like to input a formula to count the number of "U" values in the row starting at K8 continuously across that row as long as they are within the dates k5 (01/11/21) increaasing daily L5 (02/11/21), m5 (03/11/21) and so on.

My criteria would be that i only want the "U" values which are within the dates on my main sheet in F5 (admission date) and G5 (discharge date) any "U" values outside of these dates are not to be counted

i wanted to used the date values in K5 onwards as my reference point for the formula, it works as an individual cell thing like this =IF(AND(K8="U",K5<=Main!$G6,K5>=Main!$F6),COUNTIF(K8,"U"),0) however i would like that to go across the whole row. thats where i went to find the values which match my date criteria and then find the "U" values with the index formula

=COUNTIFS(INDEX($K$5:$AAA$1000,,MATCH("U",INDEX($K8:$AAA8,1,),0)),">="&Main!$F5,INDEX($K$5:$AAA$1000,,MATCH("U",INDEX($K8:$AAA8,1,),0)),"<="&Main!$G5)

i dont know if i went down the right route or made things more complicatd than needed

thankyou for being so understanding

 

[Fluff]

Ok, how about

=COUNTIFS($K$5:$AAA$5,">="&Main!F5,$K$5:$AAA$5,"<="&Main!G5,K8:AAA8,"U")

 

[Piers]

Ok, how about

=COUNTIFS($K$5:$AAA$5,">="&Main!F5,$K$5:$AAA$5,"<="&Main!G5,K8:AAA8,"U")

much easier than what i was doing - thankyou - definitely over complicated things

 

[Fluff]

Glad to help & thanks for the feedback.