Rob,
I am struggling to visualise your data. Can you post a sample workbook
somewhere?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Rob" wrote in message
...
bob thanks for your time....
Maybe I am missing something. If I have Supplier1 in 3 columns (D:F), and
i
am trying to summarize Supplier1 in column D, then how does your formula
pick
up the other 2 columns? Thats what my reference do d7 was supposed to do.
Thanks again
"Bob Phillips" wrote:
Rob,
I've spent a lot of time looking at this trying to understand, and I'm
sure
I still don't fully :-).
I don't see where D7 comes into play to ensure that you count by
supplier
for instance.
Here is my stab at it. I think (hope!) that it is close, but I expect
that
we are not quite there yet, so please feedback.
=SUMPRODUCT(--($B$13:$B$256=$C261),--(MOD(ROW($C$16:$C$259),4)=0),$C$16:$C$2
59)*(D260=$D$7)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Rob" wrote in message
...
My apologies Bob, I miss your reply. I tired your formula and it does
return
a value but it is not calculating correctly. My spreadsheet is laid
out
as
follows( The fields have been changed for clarity, and to protect the
innocent LOL). Suppliers are listed in row 7, and there may be more
than
one
column with the same Supplier.
then 10 sales divisions below. Each division has room to enter 6
transactions per supplier with 4 cells in the column making up the
transaction. Col A is a helper column that contains the division
number
in
each of the 24 rows for that division.
d7 Supplier
b13 Div1 C13 Sold to
C14 Product
C15 Date
C16 Qty
c13:c14 repeats 5 more times and then Div2 starts.
What I am trying to accomplish with the formula.....
Below the grid for entries is a supplier summary by division.
C260=Div D260= Supplier1 e260 Supplier2
C261=1
D261 is where the formula goes that will total the "Qty" for Supplier1
for
Div1
I hope I havent added further confusion. Im sure there is another
solution,
I was trying to modify an existing formula that only looked at one
column,
and wasnt sure how to get the other columns in.
Thanks!
"Bob Phillips" wrote:
I gave you an alternative.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Rob" wrote in message
...
The helper column didnt work. Are there any other approches that
I
might
use
in order to use a different range? D595=d7:IV7
"JE McGimpsey" wrote:
All your ranges must be the same size.
Perhaps you could move --(D595=D7) out of the SUMPRODUCT:
=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),
--(RIGHT(C565:C588,5)="Rec'd"), D565:D588) * (D595=D7)
In article ,
Rob wrote:
=SUMPRODUCT(--(MOD(ROW(D565:D588),4)=3),--(D595=D7),--(RIGHT(C565:C588,5)="R
ec
'd"),D565:D588).
I have disected the fromula and applied each criteria to the
range
and
get
all 1' and 0's. Yet I still get a #value!. The sum range
contains
text and
numeric entries, but all of the matching entries (The ones
that
should
add)
are numeric.
What am I missing?
|