CountIFS being selective with criteria 1

[shmoes]

Good Morning,

I have a sheet of data sorted by employee, I need to calculate the number of occurences by job id (example 27) and hours (7.5) the formula I have so far is as follows.

=COUNTIFS(Data!H:H,27,Data!F:F,"7.5")

This appears to work fine and counts all the instances that meet this criteria. however.

I use the same formula looking for hours of "9.38" instead and it will not calculate, I can't seem to figure out why?

=COUNTIFS(Data!H:H,27,Data!F:F,"9.38")

The only other thing I would like to be able to do with this formula is count only weekdays.

~AZ

 

[judgeh59]

My first thought would be a rounding format issue...how are numbers entered?...7.5 is 7.5 but not the same as 7.52. so, if the 9.38 is really 9.382 then I don't think the match will happen...that just my first though...remember that just because you see 9.38 if it is formatted then all bets are off....(thanks Skip)....formatting doesn't change the number, just the way you see it...

Be Alert, America needs more lerts

 

[shmoes]

Of course, it didn't even occur to me that the data that I got was 9.3833333, but all I could see was 9.38!

Thank you!

Now if I can just filter weekdays and weekends, I'll be rocking!

~AZ

 

[judgeh59]

glad I could help...would a pivot table help you?....check it out....

Ernest

Be Alert, America needs more lerts

 

[shmoes]

I looked at the pivot table, but the data just doesn't read the way I want it to.. I'm sure that's due to my experience level with it. when I have some time to decate to this report I will try to spend more time on it. However with a deadline of today, it makes it hard lol. I have just seperated the data on different sheets for now, maybe someone can shed some light how to put it into a formula.

Thanks Again!

~AZ

 

[Gammachaser]

If you know the date associated with each row, you can get the information you need to tell if it is a weekend. Once you get that information you can add a column with a true or false flag (as shown below) and then use that flag as a criterion in your COUNTIFS.

Assuming your first column has the date values:

=IF(TEXT(A1,"ddd") = "Sat",False,IF(TEXT(A1,"ddd") = "Sun",False,True))

will set a True/False flag in the column containing the formula. You could just as easily set the flag to say "Weekend" and Weekday", if you would rather have that.

Someone more fluent in Excel may be able to figure out how to use conditional formatting to achieve the same result.