Empty cells , sum of it from weekday's

G

Gerard

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
 
B

Bernard Liengme

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
 
G

Gerard

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
 
B

Bernard Liengme

Ok, so for person #1 her hours (or blank) are in B2 for week1.
What cell has hours/blank for her week2?
Nearly there!
 
B

Bob Phillips

=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)
 
G

Gerard

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
 
G

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
 
B

Bernard Liengme

=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
 
B

Bob Phillips

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)
 
G

Gerard

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
 
B

Bernard Liengme

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
 
G

Gerard

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
 
G

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
 
G

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...
 
Top