#1   Report Post  
Old October 31st 04, 05:55 PM
zbert
 
Posts: n/a
Default COUNTIF Question


The following countif function works well for me but I was wondering how
I could use a date range criteria from a different column. I want to
count all ocurrences of "Metro" in the month of Otcober.

=COUNTIF('PRT Metrics'!M8:M78,"Metro")

This formula counts all occurences of the word "Metro" in the range
M8:M78.

I'd like to count all occurence within a given month.


--
zbert
------------------------------------------------------------------------
zbert's Profile: http://www.excelforum.com/member.php...o&userid=15920
View this thread: http://www.excelforum.com/showthread...hreadid=274000


  #2   Report Post  
Old October 31st 04, 06:02 PM
Aladin Akyurek
 
Posts: n/a
Default


That requires a formula for multiconditional counting with a different
function...

Assuming that 'PRT Metrics'!$N$8:$N$78 is the date range:

=SUMPRODUCT(--('PRT Metrics'!$M$8:$M$78=E2),--(MONTH('PRT
Metrics'!$N$8:$N$78)=MONTH(F2)),--(YEAR('PRT
Metrics'!$N$8:$N$78)=YEAR(F2)))

where E2 houses a condition like Metro and F2 a date condition like
10/1/04 (a true date), possibly formatted as mmm-yy to read Oct-04.

Adjust to suit.

zbert Wrote:
The following countif function works well for me but I was wondering how
I could use a date range criteria from a different column. I want to
count all ocurrences of "Metro" in the month of Otcober.

=COUNTIF('PRT Metrics'!M8:M78,"Metro")

This formula counts all occurences of the word "Metro" in the range
M8:M78.

I'd like to count all occurence within a given month.



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274000

  #3   Report Post  
Old October 31st 04, 06:14 PM
Ragdyer
 
Posts: n/a
Default

Try this, with the date in Column N:

=SUMPRODUCT((M8:M78="Metro")*(MONTH(N8:N78)=10))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"zbert" wrote in message
...

The following countif function works well for me but I was wondering how
I could use a date range criteria from a different column. I want to
count all ocurrences of "Metro" in the month of Otcober.

=COUNTIF('PRT Metrics'!M8:M78,"Metro")

This formula counts all occurences of the word "Metro" in the range
M8:M78.

I'd like to count all occurence within a given month.


--
zbert
------------------------------------------------------------------------
zbert's Profile:

http://www.excelforum.com/member.php...o&userid=15920
View this thread: http://www.excelforum.com/showthread...hreadid=274000




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
Creating an EXCEL COUNTIF formula for a range of values Pat Walsh Excel Discussion (Misc queries) 5 January 21st 05 03:57 PM
COUNTIF using formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 18th 05 08:11 PM
Pivot Table - using Countif in place of Count,Sum, etc. ErikTaylor Excel Discussion (Misc queries) 0 January 14th 05 08:51 PM
CountIF cells are not empty Wayne Excel Discussion (Misc queries) 3 January 6th 05 05:44 PM
Subtotal And Countif Sheryl Excel Discussion (Misc queries) 4 December 9th 04 08:18 PM


All times are GMT +1. The time now is 01:12 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017