Hi
I cannot get this to work:


=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="Rec'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and get
all 1' and 0's. Yet I still get a #value!. The sum range contains text and
numeric entries, but all of the matching entries (The ones that should add)
are numeric.
What am I missing?
Thanks!

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="Rec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and get
all 1' and 0's. Yet I still get a #value!. The sum range contains text and
numeric entries, but all of the matching entries (The ones that should add)
are numeric.
What am I missing?

I thikn I can add a helper column. Thanks!

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="Rec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and get
all 1' and 0's. Yet I still get a #value!. The sum range contains text and
numeric entries, but all of the matching entries (The ones that should add)
are numeric.
What am I missing?

Try the below formula.

=SUMPRODUCT((MOD(ROW(D565:D588),4)=3)*(D595=D7)*(R IGHT(C565:C588,5)="Rec'd")*(D565:D588))

Hope this does what you wanted.

Thanks,
Bill Horton

"Rob" wrote:

Hi
I cannot get this to work:


=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="Rec'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and get
all 1' and 0's. Yet I still get a #value!. The sum range contains text and
numeric entries, but all of the matching entries (The ones that should add)
are numeric.
What am I missing?
Thanks!

Rob,

You don't need a helper column

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(RIGHT(C565:C588,5)="Rec'd"),D565:
D588)*(D595=D7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
I thikn I can add a helper column. Thanks!

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:


=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and
get
all 1' and 0's. Yet I still get a #value!. The sum range contains
text and
numeric entries, but all of the matching entries (The ones that should
add)
are numeric.
What am I missing?

The helper column didnt work. Are there any other approches that I might use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="Rec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and get
all 1' and 0's. Yet I still get a #value!. The sum range contains text and
numeric entries, but all of the matching entries (The ones that should add)
are numeric.
What am I missing?

What exactlyt are you trying to do?

D565:D588 = 24 rows x 1 column
D7:IV7 (where did the ":IV7" come from?) = 1 row x 253 columns

It doesn't help much to say "the helper column didnt work" if you don't
explain what it was supposed to do, or how you tried to use it...



In article ,
Rob wrote:

The helper column didnt work. Are there any other approches that I might use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)=
"Rec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and
get
all 1' and 0's. Yet I still get a #value!. The sum range contains text
and
numeric entries, but all of the matching entries (The ones that should
add)
are numeric.
What am I missing?

I gave you an alternative.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that I might
use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:


=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and
get
all 1' and 0's. Yet I still get a #value!. The sum range contains
text and
numeric entries, but all of the matching entries (The ones that should
add)
are numeric.
What am I missing?

My apologies Bob, I miss your reply. I tired your formula and it does return
a value but it is not calculating correctly. My spreadsheet is laid out as
follows( The fields have been changed for clarity, and to protect the
innocent LOL). Suppliers are listed in row 7, and there may be more than one
column with the same Supplier.
then 10 sales divisions below. Each division has room to enter 6
transactions per supplier with 4 cells in the column making up the
transaction. Col A is a helper column that contains the division number in
each of the 24 rows for that division.

d7 Supplier

b13 Div1 C13 Sold to
C14 Product
C15 Date
C16 Qty

c13:c14 repeats 5 more times and then Div2 starts.

What I am trying to accomplish with the formula.....
Below the grid for entries is a supplier summary by division.

C260=Div D260= Supplier1 e260 Supplier2
C261=1
D261 is where the formula goes that will total the "Qty" for Supplier1 for
Div1


I hope I havent added further confusion. Im sure there is another solution,
I was trying to modify an existing formula that only looked at one column,
and wasnt sure how to get the other columns in.
Thanks!


"Bob Phillips" wrote:

I gave you an alternative.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that I might
use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:


=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range and
get
all 1' and 0's. Yet I still get a #value!. The sum range contains
text and
numeric entries, but all of the matching entries (The ones that should
add)
are numeric.
What am I missing?

Rob,

I've spent a lot of time looking at this trying to understand, and I'm sure
I still don't fully :-).

I don't see where D7 comes into play to ensure that you count by supplier
for instance.

Here is my stab at it. I think (hope!) that it is close, but I expect that
we are not quite there yet, so please feedback.

=SUMPRODUCT(--($B$13:$B$256=$C261),--(MOD(ROW($C$16:$C$259),4)=0),$C$16:$C$2
59)*(D260=$D$7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
My apologies Bob, I miss your reply. I tired your formula and it does
return
a value but it is not calculating correctly. My spreadsheet is laid out
as
follows( The fields have been changed for clarity, and to protect the
innocent LOL). Suppliers are listed in row 7, and there may be more than
one
column with the same Supplier.
then 10 sales divisions below. Each division has room to enter 6
transactions per supplier with 4 cells in the column making up the
transaction. Col A is a helper column that contains the division number
in
each of the 24 rows for that division.

d7 Supplier

b13 Div1 C13 Sold to
C14 Product
C15 Date
C16 Qty

c13:c14 repeats 5 more times and then Div2 starts.

What I am trying to accomplish with the formula.....
Below the grid for entries is a supplier summary by division.

C260=Div D260= Supplier1 e260 Supplier2
C261=1
D261 is where the formula goes that will total the "Qty" for Supplier1 for
Div1


I hope I havent added further confusion. Im sure there is another
solution,
I was trying to modify an existing formula that only looked at one column,
and wasnt sure how to get the other columns in.
Thanks!


"Bob Phillips" wrote:

I gave you an alternative.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that I
might
use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:



=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range
and
get
all 1' and 0's. Yet I still get a #value!. The sum range
contains
text and
numeric entries, but all of the matching entries (The ones that
should
add)
are numeric.
What am I missing?

bob thanks for your time....
Maybe I am missing something. If I have Supplier1 in 3 columns (D:F), and i
am trying to summarize Supplier1 in column D, then how does your formula pick
up the other 2 columns? Thats what my reference do d7 was supposed to do.
Thanks again


"Bob Phillips" wrote:

Rob,

I've spent a lot of time looking at this trying to understand, and I'm sure
I still don't fully :-).

I don't see where D7 comes into play to ensure that you count by supplier
for instance.

Here is my stab at it. I think (hope!) that it is close, but I expect that
we are not quite there yet, so please feedback.

=SUMPRODUCT(--($B$13:$B$256=$C261),--(MOD(ROW($C$16:$C$259),4)=0),$C$16:$C$2
59)*(D260=$D$7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
My apologies Bob, I miss your reply. I tired your formula and it does
return
a value but it is not calculating correctly. My spreadsheet is laid out
as
follows( The fields have been changed for clarity, and to protect the
innocent LOL). Suppliers are listed in row 7, and there may be more than
one
column with the same Supplier.
then 10 sales divisions below. Each division has room to enter 6
transactions per supplier with 4 cells in the column making up the
transaction. Col A is a helper column that contains the division number
in
each of the 24 rows for that division.

d7 Supplier

b13 Div1 C13 Sold to
C14 Product
C15 Date
C16 Qty

c13:c14 repeats 5 more times and then Div2 starts.

What I am trying to accomplish with the formula.....
Below the grid for entries is a supplier summary by division.

C260=Div D260= Supplier1 e260 Supplier2
C261=1
D261 is where the formula goes that will total the "Qty" for Supplier1 for
Div1


I hope I havent added further confusion. Im sure there is another
solution,
I was trying to modify an existing formula that only looked at one column,
and wasnt sure how to get the other columns in.
Thanks!


"Bob Phillips" wrote:

I gave you an alternative.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that I
might
use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:



=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the range
and
get
all 1' and 0's. Yet I still get a #value!. The sum range
contains
text and
numeric entries, but all of the matching entries (The ones that
should
add)
are numeric.
What am I missing?

Rob,

I am struggling to visualise your data. Can you post a sample workbook
somewhere?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
bob thanks for your time....
Maybe I am missing something. If I have Supplier1 in 3 columns (D:F), and
i
am trying to summarize Supplier1 in column D, then how does your formula
pick
up the other 2 columns? Thats what my reference do d7 was supposed to do.
Thanks again


"Bob Phillips" wrote:

Rob,

I've spent a lot of time looking at this trying to understand, and I'm
sure
I still don't fully :-).

I don't see where D7 comes into play to ensure that you count by
supplier
for instance.

Here is my stab at it. I think (hope!) that it is close, but I expect
that
we are not quite there yet, so please feedback.


=SUMPRODUCT(--($B$13:$B$256=$C261),--(MOD(ROW($C$16:$C$259),4)=0),$C$16:$C$2
59)*(D260=$D$7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
My apologies Bob, I miss your reply. I tired your formula and it does
return
a value but it is not calculating correctly. My spreadsheet is laid
out
as
follows( The fields have been changed for clarity, and to protect the
innocent LOL). Suppliers are listed in row 7, and there may be more
than
one
column with the same Supplier.
then 10 sales divisions below. Each division has room to enter 6
transactions per supplier with 4 cells in the column making up the
transaction. Col A is a helper column that contains the division
number
in
each of the 24 rows for that division.

d7 Supplier

b13 Div1 C13 Sold to
C14 Product
C15 Date
C16 Qty

c13:c14 repeats 5 more times and then Div2 starts.

What I am trying to accomplish with the formula.....
Below the grid for entries is a supplier summary by division.

C260=Div D260= Supplier1 e260 Supplier2
C261=1
D261 is where the formula goes that will total the "Qty" for Supplier1
for
Div1


I hope I havent added further confusion. Im sure there is another
solution,
I was trying to modify an existing formula that only looked at one
column,
and wasnt sure how to get the other columns in.
Thanks!


"Bob Phillips" wrote:

I gave you an alternative.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that
I
might
use
in order to use a different range? D595=d7:IV7

"JE McGimpsey" wrote:

All your ranges must be the same size.

Perhaps you could move --(D595=D7) out of the SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)

In article ,
Rob wrote:




=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).

I have disected the fromula and applied each criteria to the
range
and
get
all 1' and 0's. Yet I still get a #value!. The sum range
contains
text and
numeric entries, but all of the matching entries (The ones
that
should
add)
are numeric.
What am I missing?