Empty cells , sum of it from weekday's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, i have a weekly table monday-sunday (B, C, D and so on) for each week
of the year, persons are in A2, A3 A4 and so on, if a person is free on one
day of the week the cell is empty, now i like to have a new table wich counts
all the empty cells of a person on monday, all of them on wednesday and so
on, so that i can see over a whole year, how many times someone is free on
monday and so on.
please can anyone help me ?, best regards, Gerard
 
You have nicely described one week's data:
name of first person in A2; and a x (or other entry) in B2 if not free
Monday, in C2 if not free Tues. etc.
But were is week 2? Do we go across row 2 with mon, tue, wed, thur, fri,
sat, sun, mon, tue ..........? That is week 2 follows weeks 1 on the row.
Also is it a 6 or 7 day week (Sundays in or not)?
need this info to be able to help
best wishes
 
Hello Bernard, thx for the quick answer,
horizontal are the weekdays mo,th,we,th,fr and sat (so no sunday),
then there is a total colum, total of hours, then the next week (2) begins
with mo, th, we, th, fr and sat, total colum, and then again the next week (3)
In A2 till Ax are the names of the persons.
So a person works not on monday then this cell is empty, if he works then
there is a number (hours) in the cell.
Hope this helps ? Best regards, Gerard
 
Ok, so for person #1 her hours (or blank) are in B2 for week1.
What cell has hours/blank for her week2?
Nearly there!
 
=SUMPRODUCT((1:1="Monday")*(A1:A9="Paul")*(1:9=""))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bernard, week one (monday) for person one is in B2, B3=th, B4=we, B5=th,
B6=fr, B7=sat, B8=total of B2:B7, B9= week 2 (monday) and so on. So my new
table is for this row, add all the monday (and so on) together, only if they
are empty.
Regards, Gerard
 
Hello Bob, week one (monday) for person one is in B2, B3=th, B4=we, B5=th,
B6=fr, B7=sat, B8=total of B2:B7, B9= week 2 (monday) and so on. So my new
table is for this row, add all the monday (and so on) together, only if they
are empty.
Regards, Gerard
 
=SUMPRODUCT(--(A2:A6000="Paul"),--(B2:B6000=""))

You cannot use A:A with SUMPRODUCT

Suppose you made list on Sheet2 in A2 down of all the names
In B1:G1 put the days of the week: Mon, Tue, etc
In B2 enter
=SUMPRODUCT(--(Sheet1!$A$2:$A$6000=$A2),--(Sheet1!B$2:B$6000=""))
Copy this across the row and then copy B2:G2 down to the bottom of the list
Now you have a neat table
best wishes
 
That is completely different to what you originally said. So where are the
names?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
hi Bernard,
everytime i fill this in the message comes "#name?",
i tryed to rename and change everything, but still always the message.
i've changed everyting to A2 A2 and B2 B3, but still the same,
any suggestions ?
regards Gerard
 
Try in on a small dataset in a new worksheet.
In A1:A10 enter some A's and some B's
Put X's in some cells in column B, leave some blank
So you should have a few cells with A in column A and blank in column B;
let's find how many with
=SUMPRODUCT(--(A1:A10="X"),--(B1:B10=""))

tell me what happens
 
Hello Bernard, when i put the formula in its say's directly, somting wrong
with the formula, and it points on the "," , so by "),-" so nothing happens,
i can change the "," in a ";" then it says "#name?", i can delete the ","
then also "#name?" comes up, so whats wrong?, regards Gerard
 
Hello Bernard, from somebody else i got the formula : =30-AANTALARG(B2; I2;
P2; etc), this works also fine but you only can put 30 cells in it and not 52
from a whole year, may this helps ? Regards, Gerard
 
hello, has anyone a idee for this ??? thx....

Gerard said:
Hello Bernard, from somebody else i got the formula : =30-AANTALARG(B2; I2;
P2; etc), this works also fine but you only can put 30 cells in it and not 52
from a whole year, may this helps ? Regards, Gerard
Click to expand...
 
Back
Top