Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 114
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Countif based on two criteria

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 114
Default Countif based on two criteria

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Countif based on two criteria

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


"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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 114
Default Countif based on two criteria

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")*(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



"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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using COUNTIF to count based on multiple cell criteria. Disdan Excel Worksheet Functions 5 November 30th 07 02:59 AM
How do you create COUNTIF functions based on multiple criteria? MsBeverlee Excel Worksheet Functions 8 February 19th 07 11:25 PM
An array and countif based on criteria in each column Rochelle B Excel Worksheet Functions 1 October 25th 05 05:08 AM
Countif based on 2 criteria N E Body Excel Worksheet Functions 5 August 22nd 05 02:15 AM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 03:17 PM


All times are GMT +1. The time now is 11:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"