Countif based on two criteria

  • Thread starter Thread starter MJKelly
  • Start date Start date
M

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
 
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
 
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")*(D2:D8="PM"))
=SUMPRODUCT(($A2:$A8="y")*(D2:D8="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
 
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")*(D2:D8="PM"))
=SUMPRODUCT(($A2:$A8="y")*(D2:D8="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 -
Click to expand...

Works a treat, thanks very much.

Kind regards,
Matt
 
Back
Top