Count word in row to a particular date

[Shamz41]

Hi

I have a query. I have a badge scan file as attached. The badges numbers in column D will get scanned daily. I'm stuck in Row 5 where I need a count function to count the number of damaged cards in column A, as I scan each day. The problem is the card's status can get changed on any given day within the month Eg: from Damaged to Loan or Assigned. which I why I need a function to count on that current date.

[FlameRetired]

Try this in cell E5 and fill across.

=SUMPRODUCT(COUNTIFS(E$2:E$4,E$1,$D$2:$D$4,$D$2:$D$4,$A$2:$A$4,"Damaged"))

[Shamz41]

Hi
This worked but for example when I changed the status of badge 1 to loan on the 12 may, the total for the 11 may changes as well. Is there a way to fix the totals from not changing daily ehwn the card does gets its status reassigned.

[FlameRetired]

If I understand correctly that will require an additional column indicating date of status change for Badge 1. Then this formula should do that when Badge 1 status is changed in a later row.

=SUMPRODUCT(COUNTIFS(F$2:F$5,$E$2:$E$5,$D$2:$D$5,$D$2:$D$5,$E$2:$E$5,F$1,$A$2:$A$5,"Damaged"))

[Shamz41]

Hi
When I changed the date in column E, The tally in row 6 changes to the date that i input in column E. I need the tally to remain as I scan each day.
For example if I scan today on the 11 may and there are 1 damaged badge, F6 will show 1. Tomorrow when I scan on 12 may, and i change card no 2 to damaged status, F6 should remain as "1" and G6 should show as "2"