Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
Hi, if i'm looking for a formula which will total the cumulative values in
cells Q3:Q3000, but only if the criteria in two other sets of cells are present, ie A3:3000 has a date in the month of January and P3:P3000 has the text "KT" in it, how would I go about this? The values in Q3:3000 are in pounds, so i'd be looking for a cumulative figure if the preivous two criteria are met in other cells. Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
On Mon, 11 Feb 2008 03:36:01 -0800, GD wrote:
Hi, if i'm looking for a formula which will total the cumulative values in cells Q3:Q3000, but only if the criteria in two other sets of cells are present, ie A3:3000 has a date in the month of January and P3:P3000 has the text "KT" in it, how would I go about this? The values in Q3:3000 are in pounds, so i'd be looking for a cumulative figure if the preivous two criteria are met in other cells. Cheers Perhaps something like (not tested): =SUMPRODUCT((MONTH(A3:A3000)=1)*(P3:P3000="KT")*Q3 :Q3000) --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
Thanks Ron, i've amended it to be relevant to my sheets..
=SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008 Errors'!P3:P3000="KT")*'2008 Errors'!Q3:Q3000) as it's referring to another sheets cells, and unfortunately it's returning a result of #VALUE! Any ideas? "Ron Rosenfeld" wrote: On Mon, 11 Feb 2008 03:36:01 -0800, GD wrote: Hi, if i'm looking for a formula which will total the cumulative values in cells Q3:Q3000, but only if the criteria in two other sets of cells are present, ie A3:3000 has a date in the month of January and P3:P3000 has the text "KT" in it, how would I go about this? The values in Q3:3000 are in pounds, so i'd be looking for a cumulative figure if the preivous two criteria are met in other cells. Cheers Perhaps something like (not tested): =SUMPRODUCT((MONTH(A3:A3000)=1)*(P3:P3000="KT")*Q3 :Q3000) --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
Does any of Q3:Q3000 have text values?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "GD" wrote in message ... Thanks Ron, i've amended it to be relevant to my sheets.. =SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008 Errors'!P3:P3000="KT")*'2008 Errors'!Q3:Q3000) as it's referring to another sheets cells, and unfortunately it's returning a result of #VALUE! Any ideas? "Ron Rosenfeld" wrote: On Mon, 11 Feb 2008 03:36:01 -0800, GD wrote: Hi, if i'm looking for a formula which will total the cumulative values in cells Q3:Q3000, but only if the criteria in two other sets of cells are present, ie A3:3000 has a date in the month of January and P3:P3000 has the text "KT" in it, how would I go about this? The values in Q3:3000 are in pounds, so i'd be looking for a cumulative figure if the preivous two criteria are met in other cells. Cheers Perhaps something like (not tested): =SUMPRODUCT((MONTH(A3:A3000)=1)*(P3:P3000="KT")*Q3 :Q3000) --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
No just numerical values
"Bob Phillips" wrote: Does any of Q3:Q3000 have text values? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "GD" wrote in message ... Thanks Ron, i've amended it to be relevant to my sheets.. =SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008 Errors'!P3:P3000="KT")*'2008 Errors'!Q3:Q3000) as it's referring to another sheets cells, and unfortunately it's returning a result of #VALUE! Any ideas? "Ron Rosenfeld" wrote: On Mon, 11 Feb 2008 03:36:01 -0800, GD wrote: Hi, if i'm looking for a formula which will total the cumulative values in cells Q3:Q3000, but only if the criteria in two other sets of cells are present, ie A3:3000 has a date in the month of January and P3:P3000 has the text "KT" in it, how would I go about this? The values in Q3:3000 are in pounds, so i'd be looking for a cumulative figure if the preivous two criteria are met in other cells. Cheers Perhaps something like (not tested): =SUMPRODUCT((MONTH(A3:A3000)=1)*(P3:P3000="KT")*Q3 :Q3000) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
On Mon, 11 Feb 2008 05:17:02 -0800, GD wrote:
Thanks Ron, i've amended it to be relevant to my sheets.. =SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008 Errors'!P3:P3000="KT")*'2008 Errors'!Q3:Q3000) as it's referring to another sheets cells, and unfortunately it's returning a result of #VALUE! Any ideas? Most likely, your data is not in the expected format. If all of the values in col Q are numeric, then I'll guess there's an issue with your DATE column where one or more entries are not "real Excel dates". You can probably sort this out by, on your 2008 Errors worksheet, take an unused column and, in row 3, enter =month(a3). Fill down to a3000 and see if any of the cells return a #VALUE!. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
Hi, did what you suggested and all fields return a figure aside from S3 and
S13 (Which correspond to A3 and A13 which are the first to cells with dates in) - the strange thing is ive copied dates from other cells which are returning a figure no problem, into them, and it still reflects a #VALUE! in the S3 & S13. Both are also set in date format with the correct layout "Ron Rosenfeld" wrote: On Mon, 11 Feb 2008 05:17:02 -0800, GD wrote: Thanks Ron, i've amended it to be relevant to my sheets.. =SUMPRODUCT((MONTH('2008 Errors'!A3:A3000)=1)*('2008 Errors'!P3:P3000="KT")*'2008 Errors'!Q3:Q3000) as it's referring to another sheets cells, and unfortunately it's returning a result of #VALUE! Any ideas? Most likely, your data is not in the expected format. If all of the values in col Q are numeric, then I'll guess there's an issue with your DATE column where one or more entries are not "real Excel dates". You can probably sort this out by, on your 2008 Errors worksheet, take an unused column and, in row 3, enter =month(a3). Fill down to a3000 and see if any of the cells return a #VALUE!. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting, 2 criteria
On Mon, 11 Feb 2008 06:51:08 -0800, GD wrote:
Hi, did what you suggested and all fields return a figure aside from S3 and S13 (Which correspond to A3 and A13 which are the first to cells with dates in) - the strange thing is ive copied dates from other cells which are returning a figure no problem, into them, and it still reflects a #VALUE! in the S3 & S13. Both are also set in date format with the correct layout With the problem only being in two cells, I would adjust them manually. FIRST, make sure the cell is formatted as GEneral or as Date. SECOND, re-enter the date manually (typing it in). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting Criteria | Excel Discussion (Misc queries) | |||
counting based on criteria | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
2 Criteria Counting | Excel Worksheet Functions |