Can this be done using Sumproduct?

[agarwaldvk]

Hi everybody

I have this requirement where I am required to find the total number of
entries that meet critieria in three columns. I have used the below
listed formula to realize that :-


=SUMPRODUCT((INDEX(WE_20060319,,10)="NN01")*(INDEX(WE_20060319,,5)=1)*(((INDEX(WE_20060319,,3))<=C$3)*((INDEX(WE_20060319,,3))>(C$3-7))))

This works OK but the problem was that in the first argument there
(reproduced below) :-

((INDEX(WE_20060319,,10)="NN01")

the value of "NN01" represents the region encompassing various number
of outlets. For example, the region and outlets are related like so :-

Region Outlet Code
NN01 AUBY
NN01 AUGH
NN01 AYGR
NN02 BUYE
NN03 BJUR
NN01 AUBT

etc.etc.

The region code is in column 10 ('J') and outlet code is in column 1
('A')

The region code as such does not form a part of source data but is
available on a separate mapping worksheet which shows the association
of each of the outlets with a region code. To be able to use the
Sumproduct function, I had to actually introduced another column in the
source data worksheet (Column 'J') with matching values of the region
code from the mapping worksheet for each of the outlet code entries in
the source data.

I was wondering if this can be done using sumproduct :-

=SUMPRODUCT((INDEX(WE_20060319,,10)=A1:C8)*(INDEX(WE_20060319,,5)=1)*(((INDEX(WE_20060319,,3))<=C$3)*((INDEX(WE_20060319,,3))>(C$3-7)))).

The range "A1:C1" on the worksheet containing the formula (this
worksheet is obviously different from either the source data worksheet
or the mapping worksheet) lists all the outlets falling under the
region code "NN01".

The number of rows in the named range "WE_20060319" will always far
exceed the number of entries in any given region code.

I am reproducing an example from the XLDynamic page to indicate tdhat
this could be something that I could be looking for :-

Example 3: Instead of typing the multiple criteria into the formula,
can I have them typed into cells, and just reference the cells?

Solution: This seeemd a simple request to which a solution of
=SUMPRODUCT((B5:B63=L1:N1)*(C5:C63))
was suggested.
This failed because the requester wanted the criteria in a column, not
a row, so this required the TRANSPOSE function to incorporate in
SUMPRODUCT. This was the result
=SUMPRODUCT((B5:B63=TRANSPOSE(P4648))*(C5:C63))

which, because it uses the TRANSPOSE function, has to be entered as an
array formula.

Any suggestions please??????????



Best regards



Deepak Agarwal

 

[daddylonglegs]

Perhaps

=SUMPRODUCT((COUNTIF(A1:C8,INDEX(WE_20060319,,10))>0)*(INDEX
WE_20060319,,5)=1)*(((INDEX(WE_20060319,,3))<=C$3
*((INDEX(WE_20060319,,3))>(C$3-7)))