Counting in arrays
 

After reading a lot of answers in this group I am still stuck with
this one.

I have 4 excel columns thus:-

date initials date initial

03/01/2004 JK 05/02/2001 jw
23/05/2004 AP 12/12/2004 aw
I wish to count cells where the month and the year and the initials
are set conditions.

eg Count all JKs in january 2004
I can count on the first 2 columns with

{=SUM(IF(MONTH('2004'!$P$3:$P$998)=1,IF(YEAR('2004 '!$P$3:$P$998)=2004,IF('2004'!$R$3:$R$998="jk",1,0 ))))}

but cannot work out how to include next 2 columns.
The layout of the spreadsheet cannot be changed.
Go easy on me as I'm a pensioner trying to help a grand daughter

Hi
try:
=SUMPRODUCT(--(MONTH('2004'!$P$3:$P$998)=1),--(YEAR('2004'!$P$3:$P$998)=2004),--('2004'!$R$3:$R$998="jk"))+SUMPRODUCT(--(MONTH('2004'!$S$3:$S$998)=1),--(YEAR('2004'!$S$3:$S$998)=2004),--('2004'!$T$3:$T$998="jk"))

"stumped" wrote:

After reading a lot of answers in this group I am still stuck with
this one.

I have 4 excel columns thus:-

date initials date initial

03/01/2004 JK 05/02/2001 jw
23/05/2004 AP 12/12/2004 aw
I wish to count cells where the month and the year and the initials
are set conditions.

eg Count all JKs in january 2004
I can count on the first 2 columns with

{=SUM(IF(MONTH('2004'!$P$3:$P$998)=1,IF(YEAR('2004 '!$P$3:$P$998)=2004,IF('2004'!$R$3:$R$998="jk",1,0 ))))}

but cannot work out how to include next 2 columns.
The layout of the spreadsheet cannot be changed.
Go easy on me as I'm a pensioner trying to help a grand daughter

Also...assuming that Columns A through D contain your data...

=SUM((IF(ISNUMBER(A1:C10),(MONTH(A1:C10)=1)*(YEAR( A1:C10)=2004)*(B1:D10="JK"))))

...entered using CONTROL+SHIFT+ENTER.

Hope this helsp!

stumped Wrote:
After reading a lot of answers in this group I am still stuck with
this one.

I have 4 excel columns thus:-

date initials date initial

03/01/2004 JK 05/02/2001 jw
23/05/2004 AP 12/12/2004 aw
I wish to count cells where the month and the year and the initials
are set conditions.

eg Count all JKs in january 2004
I can count on the first 2 columns with

{=SUM(IF(MONTH('2004'!$P$3:$P$998)=1,IF(YEAR('2004 '!$P$3:$P$998)=2004,IF('2004'!$R$3:$R$998="jk",1,0 ))))}

but cannot work out how to include next 2 columns.
The layout of the spreadsheet cannot be changed.
Go easy on me as I'm a pensioner trying to help a grand daughter


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=277681

Hi,

=SUM(('2004'!$R$3:$R$998='jk')*(MONTH('2004'!$P$3: $P$998)=1)*(YEAR('2004'!$P$3:$P$998)=2004)+('2004' !$T$3:$T$998='jk')*(MONTH('2004'!$S$3:$S$998)=1)*( YEAR('2004'!$S$3:$S$998)=2004))

- Asser


--
Jazzer
------------------------------------------------------------------------
Jazzer's Profile: http://www.excelforum.com/member.php...fo&userid=4464
View this thread: http://www.excelforum.com/showthread...hreadid=277681

Excellent, what would I do without geniuses like you. Out of interest what
does the -- do in a function .
I could find no reference to it in help


+++++++++++++++++++
"Frank Kabel" wrote in message
...
Hi
try:
=SUMPRODUCT(--(MONTH('2004'!$P$3:$P$998)=1),--(YEAR('2004'!$P$3:$P$998)=2004),--('2004'!$R$3:$R$998="jk"))+SUMPRODUCT(--(MONTH('2004'!$S$3:$S$998)=1),--(YEAR('2004'!$S$3:$S$998)=2004),--('2004'!$T$3:$T$998="jk"))

"stumped" wrote:

After reading a lot of answers in this group I am still stuck with
this one.

I have 4 excel columns thus:-

date initials date initial

03/01/2004 JK 05/02/2001 jw
23/05/2004 AP 12/12/2004 aw
I wish to count cells where the month and the year and the initials
are set conditions.

eg Count all JKs in january 2004
I can count on the first 2 columns with

{=SUM(IF(MONTH('2004'!$P$3:$P$998)=1,IF(YEAR('2004 '!$P$3:$P$998)=2004,IF('2004'!$R$3:$R$998="jk",1,0 ))))}

but cannot work out how to include next 2 columns.
The layout of the spreadsheet cannot be changed.
Go easy on me as I'm a pensioner trying to help a grand daughter

Hi
'--' coerces the bolean values (TRUE/FALSE) to real numbers (1/0)

See for more details about this:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards
Frank Kabel
Frankfurt, Germany

"jocker" schrieb im Newsbeitrag
i.nl...
Excellent, what would I do without geniuses like you. Out of interest
what
does the -- do in a function .
I could find no reference to it in help


+++++++++++++++++++
"Frank Kabel" wrote in message
...
Hi
try:

=SUMPRODUCT(--(MONTH('2004'!$P$3:$P$998)=1),--(YEAR('2004'!$P$3:$P$998)
=2004),--('2004'!$R$3:$R$998="jk"))+SUMPRODUCT(--(MONTH('2004'!$S$3:$S$
998)=1),--(YEAR('2004'!$S$3:$S$998)=2004),--('2004'!$T$3:$T$998="jk"))

"stumped" wrote:

After reading a lot of answers in this group I am still stuck with
this one.

I have 4 excel columns thus:-

date initials date initial

03/01/2004 JK 05/02/2001 jw
23/05/2004 AP 12/12/2004 aw
I wish to count cells where the month and the year and the
initials
are set conditions.

eg Count all JKs in january 2004
I can count on the first 2 columns with


{=SUM(IF(MONTH('2004'!$P$3:$P$998)=1,IF(YEAR('2004 '!$P$3:$P$998)=2004,I
F('2004'!$R$3:$R$998="jk",1,0))))}

but cannot work out how to include next 2 columns.
The layout of the spreadsheet cannot be changed.
Go easy on me as I'm a pensioner trying to help a grand daughter