Sumproduct Help

[Guest]

Hi
I cannot get this to work:


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

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!

 

[JE McGimpsey]

All your ranges must be the same size.

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

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

 

[Guest]

I thikn I can add a helper column. Thanks!

All your ranges must be the same size.

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

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

 

[Guest]

Try the below formula.

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

Hope this does what you wanted.

Thanks,
Bill Horton

 

[Bob Phillips]

Rob,

You don't need a helper column

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

--

HTH

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

 

[Guest]

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]

What exactlyt are you trying to do?

D565588 = 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...

 

[Bob Phillips]

I gave you an alternative.

--

HTH

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

 

[Guest]

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]

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)

 

[Guest]

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]

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)