Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
You're use of EOM is evaluating to the NEXT month:
K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
this idea
=SUMPRODUCT(((ChecksA=EOMONTH(O6,1))*(ChecksD1))) maybe SUMPRODUCT((($K$3:$K$151=EOMONTH($K$3,1))*($A$3:$A $151<1))) -- Don Guillett SalesAid Software wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
Gentlemen,
Thanks a ton for the responses. Though neither of them worked out the way I wanted. The result is supposed to be six and I got 1. There aren't any empty cells in either of the ranges. I'm thinking that I may have an issue with date formatting..should it be in serial format? I have been working on this problem for a few days now, asked numerous people and I'm beginning think it is impossible. Does anyone want to take a look at the workbook? Biff wrote: You're use of EOM is evaluating to the NEXT month: K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
Biff wrote: You're use of EOM is evaluating to the NEXT month: K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
Try this:
Just a hunch that this is what you're trying to do: =SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1)) You want to count based on *EVERY* EOM date? Biff wrote in message ps.com... Gentlemen, Thanks a ton for the responses. Though neither of them worked out the way I wanted. The result is supposed to be six and I got 1. There aren't any empty cells in either of the ranges. I'm thinking that I may have an issue with date formatting..should it be in serial format? I have been working on this problem for a few days now, asked numerous people and I'm beginning think it is impossible. Does anyone want to take a look at the workbook? Biff wrote: You're use of EOM is evaluating to the NEXT month: K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
Has anyone every called you genius? If not, let me be the first.
Thanks a ton! Biff wrote: Try this: Just a hunch that this is what you're trying to do: =SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1)) You want to count based on *EVERY* EOM date? Biff wrote in message ps.com... Gentlemen, Thanks a ton for the responses. Though neither of them worked out the way I wanted. The result is supposed to be six and I got 1. There aren't any empty cells in either of the ranges. I'm thinking that I may have an issue with date formatting..should it be in serial format? I have been working on this problem for a few days now, asked numerous people and I'm beginning think it is impossible. Does anyone want to take a look at the workbook? Biff wrote: You're use of EOM is evaluating to the NEXT month: K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct to count two arguments
You're welcome. Thanks for the feedback!
Biff wrote in message ups.com... Has anyone every called you genius? If not, let me be the first. Thanks a ton! Biff wrote: Try this: Just a hunch that this is what you're trying to do: =SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1)) You want to count based on *EVERY* EOM date? Biff wrote in message ps.com... Gentlemen, Thanks a ton for the responses. Though neither of them worked out the way I wanted. The result is supposed to be six and I got 1. There aren't any empty cells in either of the ranges. I'm thinking that I may have an issue with date formatting..should it be in serial format? I have been working on this problem for a few days now, asked numerous people and I'm beginning think it is impossible. Does anyone want to take a look at the workbook? Biff wrote: You're use of EOM is evaluating to the NEXT month: K3 = 1/1/2006 EOMONTH(K3,1) = 2/28/2006 Maybe you want something like this: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1)) Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a problem with your second array: ($A$3:$A$151<1). So, you may want to add another array to the formula to test for that: =SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1)) Biff wrote in message oups.com... I have an excel test so please bear with me that what I am asking for may not be the best way to do this. Here is what I have been asked to do. I have daily dates from the beginning of this year to now and corresponding data. Using the sumproduct function I want to count the number of time that the data is less than 1 and at the end of the month. Here is what I have: =SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1)) I am stuck on the eomonth part..can anyone help? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count values in two columns | Excel Discussion (Misc queries) | |||
Using sumproduct to count number by date | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions |