My guess it's a simple quesion, but I'm a beginner

[danham]

If anyone could help me with this one. I can't figure it out.

Example of table (it's a working schedual):

a b c d
Day DayShift NightShift
1 Monday John Mary
2 Tuesday Steve Jack
3 Wensday Ivone Mark
....
7 Sunday Steve Mark

*this table goes like this for hole month

Problem:
I would like to count how many "sundays" in "DayShift" did employee in
example "Steve" worked this month. I figured to use COUNTIF when I
needed to count how many "DayShifts" Steve worked
(CountIf(C1:C7;"Steve"), but that included range of only one column -
even that was realy big thing for me . But now I need to count for
"Steve" in "Dayshift" only if it's "Sunday". I just can't find the
proper function. My english is not too good, so I hope you understand
what I'm trying to say.

THANK YOU!!!

 

[Guest]

put this a cell to the right of your tableyou first have to remove all
spaces from your table-steve and sunday had spaces at the end, which I
removed; also make sure you've got things spelled right)

=SUMPRODUCT(--($A$2:$A$6="Sunday"),--($B$2:$B$6="Steve"))

hth,
Dave

 

[Guest]

Hi,
Try this...
=SUM(IF( (C14:C21="steve") * (B14:B21="Sunday") , 1 ) )
(spaces are for clarity)
(remember to end with a cntrl shift enter)
peter

 

[danham]

I had to adopt that formula for the actual table I use (that one was
just an example), so I've tried Peters formula first (it looked simpler
), but it didn't work (got note that it contains an error). But when
I've used Daves it worked. Although I had to change "," sign with "*".
=SUMPRODUCT(($A$2:$A$6="Sunday"),($B$2:$B$6="Steve"))
=SUMPRODUCT(($A$2:$A$6="Sunday")*($B$2:$B$6="Steve"))

That came to my mind after I've seen Peters formula. So I have to say
thanks to both of you. This was soooo helpful since I spent few hours
on this and not geting anywhere. I'm realy greatful, thank you again.

 

[Guest]

the * had to be used because you excluded the "--" s that were in the
original formula. Either one works though. and your welcome.

 

[Bernie Deitrick]

Danham,

I would suggest using a Pivot Table:

Select your data table, then use Data / Pivot Tables and click OK till a new sheet appears.

Drag the "Day" button to the row fields, then the DayShift to both the Data Items Field and to the
Column Field. Excel will automatically generate a list of all the counts for all the people for all
the days.

If there is overlap between the Day and Night Shift, you could also drag the NightShift button to
the Data Items filed, or use another pivot table to do the NightShift (necessary if there are people
who only work one shift and not the other).

HTH,
Bernie
MS Excel MVP

 

[danham]

Wow thank you Bernie, Pivot table is really useful... I'm discovering a
hole new world . In fact it gives me exact kind of statistic I'm
trying to achieve with formulas. Although I want to know the way with
formulas, because I'm working with people who are not much into
computers, so even if they only have to refresh pivot table it might
become a problem. I want that everyone can just change name and
everything else to be fully automated.

Daniel