Countif based on two criteria

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

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" wrote:


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

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

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" wrote:

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

On Oct 13, 2:14*pm, Mike H wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -

Works a treat, thanks very much.

Kind regards,
Matt