Formula to count # emp with carry over days by function / location

[janljan]

Hello,

I have attached a small example of worksheet.
Two worksheets: summary and detail. I can not add any columns or calculations on the detail worksheet.

On summary worksheet, I need to count the number of employees that still have remaining carryover days from last year by function (sales, operation, etc) & by location.

Cost center on detail wrksht is the function; first 4 characters 3811 is sales.
Detail worksheet has 2 other columns named Carryover & Used.
I can not find a calculation that works for me.
Any help would be appreciated.

TIA
Regards,
Jan

[carsto]

In B3 enter
=SUMPRODUCT(--(TRIM(detail!$A$5:$A$18)=TRIM(summary!A3)) * --(LEFT(detail!$B$5:$B$18,4)="3811") * detail!$E$5:$E$18)

copy down
then change "3811" to the CS and OPS designation for those in the corresponding cells.

You cannot include the blank row 4 on the Detail sheet in this formula.

[janljan]

Hi,

That is not the answer and may be due to me not explaining correctly. It evens me the total number of carry over days.
Column E on detail is the number of vacation carry over days at beginning of 2017.
Column F on detail worksheet is the number of carry over days that have been used in 2017 as of date of report.
I need to find out the number of employees that still have carry over days that have not been used so it would be a calculation between E-F.

TIA,
Jan

[JeteMc]

See if this formula works for you:

=SUMPRODUCT((TRIM(detail!A$4:A$25)=A3)*(LEFT(detail!B$4:B$25,4)="3811")*(detail!E$4:E$25-detail!F$4:F$25>0))

1) Select cell B3 on the summary sheet,
2) Paste the formula into the formula bar and press the Enter key,
3) Copy by dragging the fill handle down to cell B7.
Let us know if you have any questions.

[janljan]

Hi JeteMc,

Thank you. That was the formula I needed.

Regards,
Jan

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.