If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct
I can not get the sumproduct function to work properly. I have read a number
of threads and followed the example to a tee, but function will not work. Here is my problem. I have two colums both on another work sheet. I would like to count the number of records based on two criterias, one for each column. Column one has names of vendors, column two has letters A,B,C Vendor Part abc A abc B lmn C xyz B lmn A For the criteria Vendor (lmn) and Part (C) I should count only 1 record |
#2
|
|||
|
|||
Sumproduct
Yes, with some minor alterations:
the formula is on a sheet on then the two arrays. the criteria for the for range is a cell value. This what my formula looks like and ends up with an #N/A =SUMPRODUCT(--('Item Master-BM'!$O$3:$O$1233=B10),--('Item Master-BM'!$AB$3:$AB$1233="c")) "Duke Carey" wrote: Are you using something like: =sumproduct(--(A2:a100="lmn"),--(B2:B100="C")) "6pak" wrote: I can not get the sumproduct function to work properly. I have read a number of threads and followed the example to a tee, but function will not work. Here is my problem. I have two colums both on another work sheet. I would like to count the number of records based on two criterias, one for each column. Column one has names of vendors, column two has letters A,B,C Vendor Part abc A abc B lmn C xyz B lmn A For the criteria Vendor (lmn) and Part (C) I should count only 1 record |
#3
|
|||
|
|||
Sumproduct
What results do you get with your formula? A number you believe to be wrong,
or an error? If an erroneous number, then I'd suspect extra spaces in some of the text. You can use the TRIM() function in a helper column to ensure that there are no extra spaces. See if that solves your problem. "6pak" wrote: Yes, with some minor alterations: the formula is on a sheet on then the two arrays. the criteria for the for range is a cell value. This what my formula looks like and ends up with an #N/A =SUMPRODUCT(--('Item Master-BM'!$O$3:$O$1233=B10),--('Item Master-BM'!$AB$3:$AB$1233="c")) "Duke Carey" wrote: Are you using something like: =sumproduct(--(A2:a100="lmn"),--(B2:B100="C")) "6pak" wrote: I can not get the sumproduct function to work properly. I have read a number of threads and followed the example to a tee, but function will not work. Here is my problem. I have two colums both on another work sheet. I would like to count the number of records based on two criterias, one for each column. Column one has names of vendors, column two has letters A,B,C Vendor Part abc A abc B lmn C xyz B lmn A For the criteria Vendor (lmn) and Part (C) I should count only 1 record |
#4
|
|||
|
|||
Sumproduct
The error I get is #N/A.
I don't believe it has anything to do with extra spaces, I have use the info in other formulas such as subif and countif which seem to work. I wonder if it has anything to do with using a cell value as a criteria or the fact the the arrays (ranges) are on another sheet. "Duke Carey" wrote: What results do you get with your formula? A number you believe to be wrong, or an error? If an erroneous number, then I'd suspect extra spaces in some of the text. You can use the TRIM() function in a helper column to ensure that there are no extra spaces. See if that solves your problem. "6pak" wrote: Yes, with some minor alterations: the formula is on a sheet on then the two arrays. the criteria for the for range is a cell value. This what my formula looks like and ends up with an #N/A =SUMPRODUCT(--('Item Master-BM'!$O$3:$O$1233=B10),--('Item Master-BM'!$AB$3:$AB$1233="c")) "Duke Carey" wrote: Are you using something like: =sumproduct(--(A2:a100="lmn"),--(B2:B100="C")) "6pak" wrote: I can not get the sumproduct function to work properly. I have read a number of threads and followed the example to a tee, but function will not work. Here is my problem. I have two colums both on another work sheet. I would like to count the number of records based on two criterias, one for each column. Column one has names of vendors, column two has letters A,B,C Vendor Part abc A abc B lmn C xyz B lmn A For the criteria Vendor (lmn) and Part (C) I should count only 1 record |
#5
|
|||
|
|||
Sumproduct
Comparing a range of values to the contents of a cell poses no problems for
SUMPRODUCT. Select the cell with the formula and use Tools-Formula Auditing-Evaluate Formula to see how Excel interprets the components of your formula. If that doesn't give you the info you need to solve this, post back with the results so we can anaylze it some more "6pak" wrote: The error I get is #N/A. I don't believe it has anything to do with extra spaces, I have use the info in other formulas such as subif and countif which seem to work. I wonder if it has anything to do with using a cell value as a criteria or the fact the the arrays (ranges) are on another sheet. "Duke Carey" wrote: What results do you get with your formula? A number you believe to be wrong, or an error? If an erroneous number, then I'd suspect extra spaces in some of the text. You can use the TRIM() function in a helper column to ensure that there are no extra spaces. See if that solves your problem. "6pak" wrote: Yes, with some minor alterations: the formula is on a sheet on then the two arrays. the criteria for the for range is a cell value. This what my formula looks like and ends up with an #N/A =SUMPRODUCT(--('Item Master-BM'!$O$3:$O$1233=B10),--('Item Master-BM'!$AB$3:$AB$1233="c")) "Duke Carey" wrote: Are you using something like: =sumproduct(--(A2:a100="lmn"),--(B2:B100="C")) "6pak" wrote: I can not get the sumproduct function to work properly. I have read a number of threads and followed the example to a tee, but function will not work. Here is my problem. I have two colums both on another work sheet. I would like to count the number of records based on two criterias, one for each column. Column one has names of vendors, column two has letters A,B,C Vendor Part abc A abc B lmn C xyz B lmn A For the criteria Vendor (lmn) and Part (C) I should count only 1 record |
Thread Tools | |
Display Modes | |
|
|