Countifs

[trebor1956]

I am trying to count the number of times "H" appears in a row if the date is less than or equal to today.
todays date is in cell A1, the row of dates are between R4:JR4, the letter "H" if present is in cells R4:JR4. The formula I use is
=COUNTIFS($R$4:$JR$4,"<="&$A$1,$R$4:$JR$4,"H")
I have just noticed that if there is an "H" under the date for today then this isn't added but an "H" in all dates prior to today are added. Any idea why this is?

 

[FormR]

Hi, do you also have times in R4:JR4?

If so - you could try..

=COUNTIFS($R$4:$JR$4,"<"&$A$1+1,$R$4:$JR$4,"H")

 

[trebor1956]

Hi, do you also have times in R4:JR4?

If so - you could try..

=COUNTIFS($R$4:$JR$4,"<"&$A$1+1,$R$4:$JR$4,"H")

Hi,

No times just the date. My own logic would suggest if the date in R4:JR4 is either today or before today and has an "H" it should be counted, but it doesn't seem to be. I can't understand it.

 

[FormR]

OK - hang on, just noticed that your checking the same cells for the date and the letter H.

Perhaps you can post some example data and expected results as I can't see how the formula works at all, even for dates prior to today?

 

[trebor1956]

OK - hang on, just noticed that your checking the same cells for the date and the letter H.

Perhaps you can post some example data and expected results as I can't see how the formula works at all, even for dates prior to today?

OK - hang on, just noticed that your checking the same cells for the date and the letter H.

Perhaps you can post some example data and expected results as I can't see how the formula works at all, even for dates prior to today?

=COUNTIFS($J

OK - hang on, just noticed that your checking the same cells for the date and the letter H.

Perhaps you can post some example data and expected results as I can't see how the formula works at all, even for dates prior to today?

I think I made in a mistake in the first question I posted. The formula below is what is there, the dates are in row 2 the letter "H" if present is in row 4.
=COUNTIFS($R$2:$JR$2,"<="&$A$1,$R$4:$JR$4,"H")

 

[FormR]

OK let's do some trouble shooting then..

What do these return when placed in a spare cell..?
1.

=A1=TODAY()

2.

=R2=TODAY()

- change R2 to a cell that you believe contains today's date and has a H in row 4
3.

=R4="H"

- change to the cell to reference the same column as 2

 

[trebor1956]

OK let's do some trouble shooting then..

What do these return when placed in a spare cell..?
1.

=A1=TODAY()

2.

=R2=TODAY()

- change R2 to a cell that you believe contains today's date and has a H in row 4
3.

=R4="H"

- change to the cell to reference the same column as 2

1. says todays date
2. says TRUE
3. I'm not sure what you want me to do for 3

 

[trebor1956]

Massive apologies to all those who are trying to help me. I have just spotted where I have been going wrong. All sorted, thank you for trying to help.

 

[FormR]

All sorted, thank you for trying to help.

- good to hear, thanks for letting us know.