Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Counting items in one column based on criteria in another column

I am looking to calculate the total number of students enrolled in each class
on each day the class is offered:

Column A lists the student's name
Column B lists the class the student is taking
Column C lists the date the class will be taken

So, I want to find out how many students are taking class X on Monday, then
how many are taking it on Tuesday, etc., for each class Y and Z and so on.

As a formula I was thinking along the lines of:

Count the number of times X appears in Column B if the date in Column C is
Monday

I've tried using IF and COUNTIF, but can't seem to get the syntax to work,
so I'm thinking I'm going about it all wrong.

Any suggestions? It would be most appreciated! Thank you!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default Counting items in one column based on criteria in another column

One way:

=SUMPRODUCT(--(B2:B100="X"),--(WEEKDAY(C2:C100)=2))

HTH,
Paul

--

"luttona" wrote in message
...
I am looking to calculate the total number of students enrolled in each
class
on each day the class is offered:

Column A lists the student's name
Column B lists the class the student is taking
Column C lists the date the class will be taken

So, I want to find out how many students are taking class X on Monday,
then
how many are taking it on Tuesday, etc., for each class Y and Z and so on.

As a formula I was thinking along the lines of:

Count the number of times X appears in Column B if the date in Column C is
Monday

I've tried using IF and COUNTIF, but can't seem to get the syntax to work,
so I'm thinking I'm going about it all wrong.

Any suggestions? It would be most appreciated! Thank you!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Counting items in one column based on criteria in another column

In pre-2007 versions:
COUNTIF allows only one criterion
=SUMPRODUCT(--(B1:B100="X"),--(C1:C100="Monday"))
The double negation -- converts Boolean FALSE/TRUE into numbers 0/1

SUMPROIDUCT (pre XL2007) does not work with full columns so do not use B:B
and C:C but use an actual range
For more details, Google with "SUMPRODUCT Excel"

In XL2007 - read Help on COUNTIFS which allows many criteria
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"luttona" wrote in message
...
I am looking to calculate the total number of students enrolled in each
class
on each day the class is offered:

Column A lists the student's name
Column B lists the class the student is taking
Column C lists the date the class will be taken

So, I want to find out how many students are taking class X on Monday,
then
how many are taking it on Tuesday, etc., for each class Y and Z and so on.

As a formula I was thinking along the lines of:

Count the number of times X appears in Column B if the date in Column C is
Monday

I've tried using IF and COUNTIF, but can't seem to get the syntax to work,
so I'm thinking I'm going about it all wrong.

Any suggestions? It would be most appreciated! Thank you!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Counting items in one column based on criteria in another colu

Thank you! This solved the problem brilliantly!

"Bernard Liengme" wrote:

In pre-2007 versions:
COUNTIF allows only one criterion
=SUMPRODUCT(--(B1:B100="X"),--(C1:C100="Monday"))
The double negation -- converts Boolean FALSE/TRUE into numbers 0/1

SUMPROIDUCT (pre XL2007) does not work with full columns so do not use B:B
and C:C but use an actual range
For more details, Google with "SUMPRODUCT Excel"

In XL2007 - read Help on COUNTIFS which allows many criteria
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"luttona" wrote in message
...
I am looking to calculate the total number of students enrolled in each
class
on each day the class is offered:

Column A lists the student's name
Column B lists the class the student is taking
Column C lists the date the class will be taken

So, I want to find out how many students are taking class X on Monday,
then
how many are taking it on Tuesday, etc., for each class Y and Z and so on.

As a formula I was thinking along the lines of:

Count the number of times X appears in Column B if the date in Column C is
Monday

I've tried using IF and COUNTIF, but can't seem to get the syntax to work,
so I'm thinking I'm going about it all wrong.

Any suggestions? It would be most appreciated! Thank you!



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
Counting items in one column based on criteria in another kmc103 Excel Worksheet Functions 4 May 15th 08 09:17 PM
Counting entries in column based on condition in another column RobertR Excel Worksheet Functions 1 February 8th 07 04:54 PM
sum items in a column based on criteria located in other columns Kurt Excel Discussion (Misc queries) 1 August 2nd 06 05:12 PM
move contents of column C based on criteria related to column A Debra Excel Discussion (Misc queries) 2 December 27th 05 11:25 PM
Counting & Summing based on criteria on another column Chicago D Excel Discussion (Misc queries) 2 August 25th 05 06:58 PM


All times are GMT +1. The time now is 06:04 AM.

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"