Hello.
I want to create a spreadsheet for our farm that will help monitoring absentee workers (there is a lot of absenteeism in agriculture).
The "Data table" has the raw data collected every week:
1. As you can see, Kate was present in week 50 (202050) but didn't come to work any day in week 51 (202051).
I would like to create a formula so that when I have the accumulated info per week, in week 51 it will return "blank" for Kate, instead of "0". "0" as data would actually mean that the person came to work every day, not that they didn't come all week. You can see the "Summary table" for this.
The formula I am using in B4 of the "Summary table" returns "0" when there is no actual data. I would like it to stay blank, or to have the option of putting a text like "n/a" (not applicable).
In B4 of the "Summary table" I am using: =(SUMIFS('Data table'!$C:$C,'Data table'!$A:$A,B$3,'Data table'!$B:$B,$A3)
Thanks, and happy holidays!
Summary table
Data table
I want to create a spreadsheet for our farm that will help monitoring absentee workers (there is a lot of absenteeism in agriculture).
The "Data table" has the raw data collected every week:
1. As you can see, Kate was present in week 50 (202050) but didn't come to work any day in week 51 (202051).
I would like to create a formula so that when I have the accumulated info per week, in week 51 it will return "blank" for Kate, instead of "0". "0" as data would actually mean that the person came to work every day, not that they didn't come all week. You can see the "Summary table" for this.
The formula I am using in B4 of the "Summary table" returns "0" when there is no actual data. I would like it to stay blank, or to have the option of putting a text like "n/a" (not applicable).
In B4 of the "Summary table" I am using: =(SUMIFS('Data table'!$C:$C,'Data table'!$A:$A,B$3,'Data table'!$B:$B,$A3)
Thanks, and happy holidays!
Summary table
Employee | 202051 | 202050 |
---|---|---|
John | 2 | 0 |
Mary | 4 | 5 |
Kate | SHOULD BE BLANK, OR OPTIONALLY SAY "N/A" | 1 |
Data table
Week number (YYYYWW) | Employee | Absences |
---|---|---|
202050 | John | 0 |
202050 | Mary | 5 |
202050 | Kate | 1 |
202051 | John | 2 |
202051 | Mary | 4 |