Countif Date falls within Range that's <24 hrs

[vzwtester]

I've looked and I think I'm close but no cigar.


I'm trying to figure out how to Countif a range that represents each month. There is no constant for each month. I mean that there isn't a set amount of data for each. January could have 22 cells, Feb could have 20. It changes from month to month. Currently, I have to manually update the formula in column D to match the range that corresponds to each date.

What I would like is a formula that would would find the end of data for January and count if it's <24hrs.

Book32.xlsx

[Ace_XL]

Try

=SUMPRODUCT((TEXT($A$2:$A$48,"mmmm")="January")*($B$2:$B$48<24))
or
=SUMPRODUCT((MONTH($A$2:$A$48)=1)*($B$2:$B$48<24))

[Jonmo1]

Try this in D2 and filled down to D12
=COUNTIFS(A:A,">="&C2&1,A:A,"<"&C3&1,B:B,"<24")

D13 will have to get adjusted to
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&C13&31,B:B,"<24")

[vzwtester]

Ace_XL; It worked but partially. I think it was counting the blanks when I changed the range to 5000.

Try this in D2 and filled down to D12
=COUNTIFS(A:A,">="&C2&1,A:A,"<"&C3&1,B:B,"<24")

D13 will have to get adjusted to
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&C13&31,B:B,"<24")

That works Jonmo1!

Thank you.

[Jonmo1]

D13 will have to get adjusted to
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&C13&31,B:B,"<24")

Correction
D13 would have to be
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&(C13&31)+1,B:B,"<24")

[vzwtester]

How do you change that code to look at >24 and <48?

Once I have that, it will be done. Freaking awesome!

[Ace_XL]

=SUMPRODUCT((MONTH($A$2:$A$48)=1)*($B$2:$B$48>24)*($B$2:$B$48<48))

blanks will get taken care of due to the extra conditions

EDIT: rephrased

[Jonmo1]

=SUMPRODUCT((MONTH($A$2:$A$48)*1=1)*($B$2:$B$48>24)*($B$2:$B$48<48))

blanks will get taken care of

I don't think that will make it ignore blanks in A
MONTH(blankcell) = 1
1*1 = 1 = True

The new formula ignores blanks because of the dual criteria on B (> 24 AND < 48)
But the original with just the 1 criteria <24, it would still count blanks in column A.

[Jonmo1]

For my syntax, to do > 24 and < 48

Try this in D2 and filled down to D12
=COUNTIFS(A:A,">="&C2&1,A:A,"<"&C3&1,B:B,">24",B:B,"<48")

D13 will have to get adjusted to
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&(C13&31)+1,B:B,">24",B:B,"<48")

[vzwtester]

Forgive my ignorance Jonmo1. What do I change to count values >24 and <48?

Currently I have

Please Login or Register  to view this content.

[Teethless mama]

Correction
D13 would have to be
=COUNTIFS(A:A,">="&C13&1,A:A,"<"&(C13&31)+1,B:B,"<24")

Your formula will fail if months are not in ordered

Try it this way:

=COUNTIFS(A:A,">="&C2&1,A:A,"<"&EDATE(C2&1,1),B:B,"<24")

[Jonmo1]

Forgive my ignorance Jonmo1. What do I change to count values >24 and <48?

Currently I have

Please Login or Register  to view this content.

I had left out an adjustment to the first formula in my last post.
I've corrected it now, refresh your browser and see post #9

[vzwtester]

You guys are wonderful. Thank you for helping with this.

I'm come across one more issue, then I'm done I promise. How do you count the data in the same range for each month that shows NC or 0?

[vzwtester]

My code is like this at the moment but it's hard coded.

Please Login or Register  to view this content.

[Jonmo1]

Glad to help so far,

for the new criteria I think you need to post another book with examples and expected results.

[vzwtester]

I figured it out by using what you already provided. I just needed to count when the data said NC instead of NC and 0.

Please Login or Register  to view this content.

[Jonmo1]

Great, glad it worked out.