CODEWITHSUNDEEP
dategoogle-sheetscountgoogle-sheets-formula
Zendor
Zendor

Reputation: 3

How to count a value compare value and range of dates - google sheets

I need count value "A" when match a name and range of date is true.

This is a data sheet, where add log of events every day. Names Column A, Date Row 3

data

In other sheet show count value ("A") when name (column B) match and range of date is between in 16-06 (H2) and 23-06 (I2).

Table count

I try with countifs but not work. I think need use other function.

=COUNTIFS(Asistencia!$A$3:$NQ$39;B4;Asistencia!$A$3:$NQ$39;"A";Asistencia!$A$3:$NQ$39;">="&H$2;Asistencia!$A$3:$NQ$39;"<"&I$2)

Share test sheet: https://docs.google.com/spreadsheets/d/1K3ad3iCvbg5C0ddgaBcxu6unGeX1QltMQ9NIROABR3A/edit?usp=sharing

Upvotes: 0

Views: 228

Answers (1)

p._phidot_
p._phidot_

Reputation: 1952

Put =COUNTIF(OFFSET(Asistencia!$B$4:$H$4;MATCH($B4;Asistencia!$A$4:$A$39;0)-1;MATCH(H$2;Asistencia!$B$3:$NQ$3;0)-1);"A") in H4 of Trabajo Sheet, and drag downward + left.

Idea : use countif() (same as OP's), with "shiftable" range. use match() with offset() to achieve the range "shift".

Please share if it works/not.

Upvotes: 1

Related Questions