Excel MT142 p2: Array Formula for Counting Dates

Hello,

I've been searching all over to find a way to do this, but I haven't been able to get anything to work. I am using Excel 2003.

Here is my setup. I have an array that has a student name on the left-most column, then a series of columns with dates which correspond to the date that student completed each event.

Name Event1 Event2 Event3 ... EventN
----------------------------------------
Name1 Date1 Date2 DateN
Name2 Date3

I can accomplish this with COUNTIF assuming that the array stays constant and I can manually input the row associated with each name. I am using =COUNTIF($A6:$AV6,"

i'm trying to create a formula that will count the number of times a month occurs on an alternate tab.

i think i'd want to use a countif formula, but the "criteria" is where i'm getting confused/frustrated.

my dates are recorded in mm/dd/yyyy format. I want my counting formula to give me a total of all the January dates, all the February dates, etc. all from the same worksheet.

i know someone's got it figured out and i'd love to talk to that person and get some help!!! lol.

thanks!

range1-> 1 2 3 1 2 3
range2-> a c a a b a

How do I count duplicates within an array formula?

Say I generated an array formula range2= "a"*range1 I would get an array
{1,0,3,1,0,3}

How do I write the formula to search within that array to count the number of entries that appear exactly twice and how high it is. (3 for the example above. Second highest is 1).

OK, so maybe I'm missing something pretty obvious, but I can't figure the syntax around this...

I have Date's in A1:A10000, but some cells in the range are blank.

I want to know the count of how many times each month is represented, so I have this

Code:

{=COUNT(IF(MONTH('2007'!$A$2:$A$10000)=1,0))}

Alas, this works for every month BUT January because MONTH() of a blank cell is 1 so it counts the blank cells as January.

The work around is to subtract the sum of all the other months from COUNT(A1:A10000), but is there another way to write to formula to eliminate counting the blanks as JANUARY?

Thanks,

dB

I am recording on an excel spreadsheet various dates in one column reflecting when an activity was performed (in a second column). I would like to count how many activities were performed in each month.

EXAMPLE SPREADSHEET

COLUMN A B
ROW
1 2008 January 01, Tuesday Activity A
2 2008 January 02, Wednesday Activity B
3 2008 January 03, Thursday Activity C
4 2008 January 04, Friday Activity A
5 2008 January 05, Saturday Activity B
6 2008 February 06, Wednesday Activity C
7 2008 February 07, Thursday Activity A
8 2008 February 08, Friday Activity B
9 2008 February 09, Saturday Activity C
10 2008 February 10, Sunday Activity A
11 2008 February 11, Monday Activity B
12 2008 March 12, Wednesday Activity C
13 2008 March 13, Thursday Activity A
14 2008 March 14, Friday Activity B
15 2008 March 15, Saturday Activity C
16 2008 March 16, Sunday Activity A
17 2008 March 17, Monday Activity B
18 2008 March 18, Tuesday Activity C
19 2008 March 19, Wednesday Activity A


MONTH TOTALS TOTALS (Activities per Month)
(general hits per month) Activity A B C
January
February
March
April
May
June
July
August
September
October
November
December

I would like excel to calculate for me how many times January shows, February shows, March shows, etc. as "general hits per month".
I would also like excel to calculate how many times each Activity (A, B, or C) shows in a given month.

Hello, i am relatively new to the use of array formulae, but am starting to become aware of their huge power and abilities over regular formulae.

I produce and recieve spreadsheets which can have hundereds of datasets in ranging from 1- 30 pieces of data, Each dataset is seperated spatialy, and as such i beilieve an array formula could be used to return the size of the datasets. I need an array formulae which can essentially count the number of cells in each dataset, and return the value where the rank = 1.

Please see example.

Many thanks for any contributions.

Ad

Hi

I have a conditional format that uses the following formula:

=IF(C1VLOOKUP($B1,NamedRange,1,FALSE),1,0)

This is applied on range C:C

'NamedRange' is Column C on another sheet within the same workbook.

This conditional format is used to highlight entries on one sheet that don't match the other (taking into account that the two sheets are not necessarily sorted in the same order, but column B contains the unique reference numbers)

Is there any way I can include this formula in some sort of CountIf so that I can also present a total showing how many cells have been matched (or not matched as the case may be)?

Greetings!
I am using the following SUMPRODUCT formula to count how many “DIST”, “MFRM”, and "Both" (i.e., MFRM and DIST) type applications exist for a given Director (John Doe in the example subset provided). The formula is working fine: SUMPRODUCT(--(Data5!$C$2:$C$9999="JohnDoe"),--(Data5!$D$2:$D$9999="DIST")) [the result is 4 for “DIST” in my sample data below]

What I need: I’m looking for 3 formulas that would give me the counts of the following for a given Director's name:
1 - Unique DIST “active” app ID’s for JohnDoe
2 - Unique MFRM “active” app ID’s for JohnDoe
3 - Unique count of app Id’s that have both active MFRM and DIST subapps for JohnDoe.

So, in the example data provided,
A21681 is to be counted as a “DIST” application since their are two “active” subapps listed for it and they are both of Origin “DIST”. (The Inactive and Decom should not be considered since I am only interested in "active" subapps.)
A21705 would be counted as “both” ,since it has two active subapps – 1 being MFRM and 1 DIST.

Hope that makes sense… thanks! I imagine it would be some sort of an array formula (not my area of expertise)

Here is a sample for cols A - E of my data (actual data is thousands of rows):
AppID SubApp Director Origin Status
A21681 SApp17 JohnDoe DIST Active
A21681 SApp22 JohnDoe DIST Active
A21681 SApp31 JohnDoe MFRM Decom
A21681 SApp44 JohnDoe DIST Inactive
A21705 Sapp65 JohnDoe DIST Active
A21705 Sapp21 JohnDoe MFRM Active

Hi,


Can anyone help me, i am trying to write a formula that will count a selection of cells for dates that are within 30 days of today date?

Hi there!

I have a long list of dates (in the format dd/mm/yy) and I want to be able to count the number of dates that appear in certain weeks.

So, for example, I want to count how many dates in the list appear between the 1st of April and the 7th of April.

Is there a nice easy formula to do this?

Thanks!