Sumproduct Help

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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 <[email protected]>,
> Rob <[email protected]> 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?
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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?
> > > > >
> > >
> > >
> > >
>
>
>

Try the below formula.

=SUMPRODUCT((MOD(ROW(D565:D588),4)=3)*(D595=D7)*(RIGHT(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!
>

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 <[email protected]>,
> Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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?
> > >
>
>
>

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 <[email protected]>,
Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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?
> > > >
> >
> >
> >

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

Try the below formula.

=SUMPRODUCT((MOD(ROW(D565:D588),4)=3)*(D595=D7)*(RIGHT(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!
>

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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 <[email protected]>,
Rob <[email protected]> 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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

Try the below formula.

=SUMPRODUCT((MOD(ROW(D565:D588),4)=3)*(D595=D7)*(RIGHT(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!
>

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

Try the below formula.

=SUMPRODUCT((MOD(ROW(D565:D588),4)=3)*(D595=D7)*(RIGHT(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!
>

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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 <[email protected]>,
Rob <[email protected]> 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?

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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?
> > > > >
> > >
> > >
> > >
>
>
>

I gave you an alternative.

--

HTH

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


"Rob" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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?
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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 <[email protected]>,
> Rob <[email protected]> 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?
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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 <[email protected]>,
> Rob <[email protected]> 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?
>

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> Rob <[email protected]> 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)*(RIGHT(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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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?
> > >
>
>
>

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> Rob <[email protected]> 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)*(RIGHT(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!
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > Rob <[email protected]> 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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]>,
> > > > Rob <[email protected]> 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?
> > > >
> >
> >
> >

I gave you an alternative.

--

HTH

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


"Rob" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > Rob <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 <[email protected]>,
> > > > > Rob <[email protected]> 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?
> > > > >
> > >
> > >
> > >
>
>
>

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 <[email protected]>,
Rob <[email protected]> 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 <[email protected]>,
> > Rob <[email protected]> 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?
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 <[email protected]>,
> > > > > > Rob <[email protected]> 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 <[email protected]>,
> Rob <[email protected]> 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?
>