How to link data from one tab to a formula in another

Hi

I have a sheet with 2 tabs. The formula in tab 1 in dependent on the data in tab 2. I need to add another criteria to my formula which references specific text but I'm not sure how to proceed.

My current formula is =SUMIFS(TOIL!E:E,TOIL!D:D,">="&$C$2,TOIL!D:D,"<="&$G$2,TOIL!D:D,"<=" &NOW(), TOIL!C:C,A5,C5). The additional piece I need is for this to apply only when the text 'BH Toil Accrued' is noted in Col G of the second tab

In short, in Tab 'Employees', I would like to count in Col D (from D5 onwards) the number hours accruing from Col E (Tab 'Toil') where the text in Col F says either 'BH TOIL accrued' or BH TOIL taken'

In Tab 'Employees', I would like to count in Col C (from C5 onwards) the number hours accruing from Col E (Tab 'Toil') where the text in Col G says either 'TOIL accrued' or 'TOIL taken'

Thanks in advance for any help offered

Siobhan

Has this data been desensitised??? |If not, remove it, desensitise it and then post the attachment again.

It should be simply a case of adding another criterion:

TOIL!G:G,"BH Toil Accrued"

However, your formula seems to have an extra section that should make it throw an error message. What is the C5 doing there?

=SUMIFS(TOIL!E:E,TOIL!D:D,">="&$C$2,TOIL!D:D,"<="&$G$2,TOIL!D:D,"<=" &NOW(), TOIL!C:C,A5,C5)

in C5

=SUMPRODUCT(SUMIFS(TOIL!$E:$E,TOIL!$D:$D,">="&$C$2,TOIL!$D:$D,"<="&$F$2,TOIL!$D:$D,"<=" &NOW(), TOIL!$C:$C,A5,TOIL!$F:$F,{"BH TOIL accrued","BH TOIL taken"}))

in D5

=SUMPRODUCT(SUMIFS(TOIL!$E:$E,TOIL!$D:$D,">="&$C$2,TOIL!$D:$D,"<="&$F$2,TOIL!$D:$D,"<=" &NOW(), TOIL!$C:$C,B5,TOIL!$F:$F,{"TOIL accrued","TOIL taken"}))

BUT check your data as it has trailing blanks in Column F "BH TOIL accrued " vs BH TOIL accrued" and and be consistent wth text "Taken" vs "taken"