Countif based on two criteria

[MJKelly]

Hi,

I have a number of duties which I have broken down by tasks. I want
to summarise the amount of different tasks for each time window for
each day. I have managed to do this with the following.

=COUNTIF(Monday!H$4:H$1003,'All Shifts'!$B3)

However,

I have now moved all duties into a single worksheet (instead of 7
seperate worksheets - one for each day). I have added columns to
state whether the duty occurs on a given day by putting "Y" in the
necessary column.

How do i use the above formula, but adapt it to check the status of
the day of week column? I would use a table for day of the week, so
each formula would be adjusted to check the appropriate day of week
column.


Hope you can help,
Matt

 

[Mike H]

Hi,

It's probably going to be sumproduct but without seeing your data layout
then it's impossible to be precise. Does this get you mocing in the right
direction?

=SUMPRODUCT((H1:H30="xxx")*(J1:J30="Y"))

Post back with some sample data

Mike

 

[MJKelly]

Thanks, but it's not having the desired effect.

Mon Tue Wed 06:00 06:10 06:20
Y Y Y PM PM PM
Y Y PM XD PM


The above should result in a table displaying monday data as follows

06:00 06:10 06:20
PM 2 1 2
XD 0 1 0

Hope this sheds some light?
Matt

 

[Mike H]

Hi,

I built your table in A1 - F3

A B C D E
F
Mon Tue Wed 06:00 06:10 06:20
y y y PM PM PM
Y Y PM XD PM


and got the reuslt below with this formula
=SUMPRODUCT(($A2:$A8="y")*(D28="PM"))
=SUMPRODUCT(($A2:$A8="y")*(D28="XD"))
The 2 formula go in the cells marked F and drag right

06:00 06:10 06:20
PM F 1 2
XD F 1 0

Mike

 

[MJKelly]

Hi,

I built your table in A1 - F3

A               B               C               D           E              
 F
Mon     Tue     Wed     06:00   06:10   06:20
y       y       y       PM      PM      PM
Y               Y       PM      XD      PM

and got the reuslt below with this formula
=SUMPRODUCT(($A2:$A8="y")*(D28="PM"))
=SUMPRODUCT(($A2:$A8="y")*(D28="XD"))
The 2 formula go in the cells marked F and drag right

        06:00   06:10   06:20
PM      F       1       2
XD      F       1       0

Mike









- Show quoted text -

Works a treat, thanks very much.

Kind regards,
Matt