Dual count if statement (two cells)

[claven123]

I have two colums of data, a time is say A1 and a unit name in A2, 150 or so rows of this.

Currently I'm doing a count if statement on the time portion.

=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59")

This gives me the count for that time period, I have one for the 24 hours in a day, works great.

I need to make a count if statement to include the parameters of the time and the unit.

The output I'm looking for is GSU had 12 events during the 5pm time frame.

=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59") AND ("GSU")(

Thanks,

D

 

[jarjarbingie]

I have two colums of data, a time is say A1 and a unit name in A2, 150 or so rows of this.

Currently I'm doing a count if statement on the time portion.

=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59")

This gives me the count for that time period, I have one for the 24 hours in a day, works great.

I need to make a count if statement to include the parameters of the time and the unit.

The output I'm looking for is GSU had 12 events during the 5pm time frame.

=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59") AND ("GSU")(

Thanks,

D

try:

=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59",unitcolumn,"GSU")

 

[claven123]

I get an #NAME? error.....

Then I used AND
COUNTIFS(B250:B417,">=12:00",B250:B417,"<=12:59")AND("GSU") Excel says error and suggest this...

=COUNTIFS(B250:B417,">=12:00",B250:B417,"<=12:59")*AND("GSU")

Which gives me a #VALUE! error.

I feel I need to define the range of cells to search for the "GSU" so I added *AND(B250:B417,"GSU") at the end and now get the #VALUE! error.


D

 

[jarjarbingie]

I get an #NAME? error.....

Then I used AND
COUNTIFS(B250:B417,">=12:00",B250:B417,"<=12:59")AND("GSU") Excel says error and suggest this...

=COUNTIFS(B250:B417,">=12:00",B250:B417,"<=12:59")*AND("GSU")

Which gives me a #VALUE! error.

I feel I need to define the range of cells to search for the "GSU" so I added *AND(B250:B417,"GSU") at the end and now get the #VALUE! error.


D

you have to change the 'unitcolumn' in the formula to the actual column where you match GSU..

 

[Peter_SSs]

you have to change the 'unitcolumn' in the formula to the actual column where you match GSU..

... and the rows have to match the rows used for the time.

So if the time is in column B and the units are in column C it would be something like


=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59",C250:C417,"GSU")

 

[claven123]

[COLOR=#333333]=COUNTIFS(B250:B417,">=17:00",B250:B417,"<=17:59",C250:C417,"GSU")[/COLOR]

That did the trick....

I was working towards that direction, the last thing I tried was using the two column range B250:C417 and it wouldn't work.

Tried to do this with a pivot table, didn't work either.


Thanks

D

 

[Peter_SSs]

That did the trick....

Good news!