Count specific values across multiple columns based on date in another column

[K1600]

I have two tables, the first one (Table_1) has headers of - 'Date', 'OT 1', ''OT 2', 'OT 3' and so on up to 'OT 10' (there are a few other columns but they are not relevant to this bit) This table is used to record which staff have done overtime and on which date and their colleague number is entered into one of the 'OT' columns once they are nominated for it.

The second table (Table_2) is one that I want to use to identify how many times in the last 1 month, 3 months and 6 months a person has been given overtime to make it fair when giving out additional overtime. This table has 4 columns, 'Colleage Number', '1 Month', '3 Months' and '6 Months.

I am after a formula to count how many times the 'Colleague Number' appears in the columns 'OT 1' to 'OT 10' based on the 1, 3 and 6 month date ranges. A CountIf works fine across the multiple columns but as soon as I change it to a CountIfs and add the date criteria I get an error.

Any help would be greatly appreciated.

 

[n8te]

Hello.

I am not sure I quite understand the way your worksheet is organized, but is this a viable solution for you?

Here is the formula i Found based on the worksheet organization showed here below :

=SUM((table_1[date]>=TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1))*(table_1[[OT_1]:[OT_10]]=[@ID]))

you can replace this part TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1)) with the date of your choice.

TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1))

 

[K1600]

Hello.

I am not sure I quite understand the way your worksheet is organized, but is this a viable solution for you?

Here is the formula i Found based on the worksheet organization showed here below :

=SUM((table_1[date]>=TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1))*(table_1[[OT_1]:[OT_10]]=[@ID]))

you can replace this part TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1)) with the date of your choice.



View attachment 86607
TODAY()-(365,25/12)*LEFT(Table2[[#Headers];[1Mth]];1))

Hi,

The worksheet is organised exactly as you suggested. I've tried using that formula but it errors on the '25' in the formula.

I'm not sure if it's because I'm using a mobile version of Office365 because I'm only on my tablet at the minute but I can't seem to get round it or work out why it's erroring.

 

[n8te]

I am not using online version but i guess it is a translating mistake between decimal european format and us. Try changing the , by a .

 

[K1600]

I am not using online version but i guess it is a translating mistake between decimal european format and us. Try changing the , by a .

Hello,

No, it doesn't like that either, it then doesn't like the 365 bit. I've not had any issues with the version on my Tab before but Iwill give it a whirl when I have my PC back next week and see if it works on there.

Thanks again.