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

Sumproduct
 

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

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

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

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

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