Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditions in sumproduct
Hi to everyone,
I have just discovered sumproduct for multiple conditions in sum and count! It´s great, but cannot make it work... I have a table and need to count the values in a row which are not blank, but only starting from a certain value of a list in another row (set by a reference in another cell). What i´m trying is: =SUMPRODUCT((K8:CQ8 "&B7")*(K9:CQ9 <" ")) ---B7 is the ref cell containing the value from which i have to start counting the non blank cells In the present situation (in my worksheet), there is only one value in the whole row (which is in a column further than the value in the second condition). This should give me 1 but gives 0 and I cannot understand why! If anyone can help, it would be greatly apreciated! Thanks, Antonio |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditions in sumproduct
=SUMPRODUCT((K8:CQ8B7)*(K9:CQ9 <""))
SUMPRODUCT ain't SUMIF, you don't concatenate the operator and the cell like that. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Antonio" wrote in message ... Hi to everyone, I have just discovered sumproduct for multiple conditions in sum and count! It´s great, but cannot make it work... I have a table and need to count the values in a row which are not blank, but only starting from a certain value of a list in another row (set by a reference in another cell). What i´m trying is: =SUMPRODUCT((K8:CQ8 "&B7")*(K9:CQ9 <" ")) ---B7 is the ref cell containing the value from which i have to start counting the non blank cells In the present situation (in my worksheet), there is only one value in the whole row (which is in a column further than the value in the second condition). This should give me 1 but gives 0 and I cannot understand why! If anyone can help, it would be greatly apreciated! Thanks, Antonio |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditions in sumproduct
Sorry, but I didnt understand the answer. I know that sumproduct is not sumif, but can be used as sumif (or countif, as in the case I need), with the extra possibility to allow to set multiple conditions. That is what I need. So what would it be the correct sintax for my problem? Thank you again to enyone who will give a help, Antonio "Bob Phillips" escreveu: =SUMPRODUCT((K8:CQ8B7)*(K9:CQ9 <"")) SUMPRODUCT ain't SUMIF, you don't concatenate the operator and the cell like that. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Antonio" wrote in message ... Hi to everyone, I have just discovered sumproduct for multiple conditions in sum and count! It´s great, but cannot make it work... I have a table and need to count the values in a row which are not blank, but only starting from a certain value of a list in another row (set by a reference in another cell). What i´m trying is: =SUMPRODUCT((K8:CQ8 "&B7")*(K9:CQ9 <" ")) ---B7 is the ref cell containing the value from which i have to start counting the non blank cells In the present situation (in my worksheet), there is only one value in the whole row (which is in a column further than the value in the second condition). This should give me 1 but gives 0 and I cannot understand why! If anyone can help, it would be greatly apreciated! Thanks, Antonio |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditions in sumproduct
Thanks, now I understood. It was a sintax error and I was using the sumif sintax. Tahanks a lot, Antonio "Bob Phillips" escreveu: =SUMPRODUCT((K8:CQ8B7)*(K9:CQ9 <"")) SUMPRODUCT ain't SUMIF, you don't concatenate the operator and the cell like that. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Antonio" wrote in message ... Hi to everyone, I have just discovered sumproduct for multiple conditions in sum and count! It´s great, but cannot make it work... I have a table and need to count the values in a row which are not blank, but only starting from a certain value of a list in another row (set by a reference in another cell). What i´m trying is: =SUMPRODUCT((K8:CQ8 "&B7")*(K9:CQ9 <" ")) ---B7 is the ref cell containing the value from which i have to start counting the non blank cells In the present situation (in my worksheet), there is only one value in the whole row (which is in a column further than the value in the second condition). This should give me 1 but gives 0 and I cannot understand why! If anyone can help, it would be greatly apreciated! Thanks, Antonio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct three conditions | Excel Worksheet Functions | |||
using sumproduct on subtotals meeting certain conditions | Excel Worksheet Functions | |||
Can wildcards be used in SUMPRODUCT conditions | Excel Worksheet Functions | |||
Multiple SumProduct conditions | Excel Worksheet Functions | |||
Sumproduct Multiple Conditions | Excel Worksheet Functions |