Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Rainfall Data | Excel Discussion (Misc queries) | |||
Counting question | Excel Discussion (Misc queries) | |||
Counting Function Dilemma | Excel Worksheet Functions | |||
counting entries between two dates? | Excel Worksheet Functions | |||
counting | Excel Worksheet Functions |