Do you know how to include a 'is one of' argument to a sumproduct function?
For example if I have a list of codes in the range 0001 to 9999 in column A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to select
in cells F1, F2 and F3. I current have to write
=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sumproduct((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Try this:

=SUMPRODUCT(((A1:A100=F1)+(A1:A100=F2)+(A1:A100=F3 ))*B1:B100)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peter" wrote in message
...
Do you know how to include a 'is one of' argument to a sumproduct
function?
For example if I have a list of codes in the range 0001 to 9999 in column
A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to
select
in cells F1, F2 and F3. I current have to write

=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sum
product((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one
single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Peter,

How about

=SUMPRODUCT( (A1:A100=F1) * (B1:B100) + (A1:A100=F2) * (B1:B100) + (A1:A100=F3) *
(B1:B100))
or
=SUMIF(A1:A100,"="&F1,B1:B100)+ SUMIF(A1:A100,"="&F2,B1:B100) +SUMIF(A1:A100,"="&F3,B1:B100)

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Peter" wrote in message
...
Do you know how to include a 'is one of' argument to a sumproduct function?
For example if I have a list of codes in the range 0001 to 9999 in column A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to select
in cells F1, F2 and F3. I current have to write
=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sumproduct((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Try

=SUMPRODUCT(SUMIF(A1:A100,F1:F3,B1:B100))

"Peter" wrote:

Do you know how to include a 'is one of' argument to a sumproduct function?
For example if I have a list of codes in the range 0001 to 9999 in column A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to select
in cells F1, F2 and F3. I current have to write
=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sumproduct((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

I like that!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"daddylonglegs" wrote in message
...
Try

=SUMPRODUCT(SUMIF(A1:A100,F1:F3,B1:B100))

"Peter" wrote:

Do you know how to include a 'is one of' argument to a sumproduct
function?
For example if I have a list of codes in the range 0001 to 9999 in
column A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to
select
in cells F1, F2 and F3. I current have to write

=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sum
product((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one
single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Try

=SUMPRODUCT(((A1:A100=F1)+(A1:A100=F2)+(A1:A100=F3 ))*(B1:B100))

this will add all if A1:A100 is F1 or F2 or F3

--
Greetings from New Zealand

"Peter" wrote in message
...
Do you know how to include a 'is one of' argument to a sumproduct
function?
For example if I have a list of codes in the range 0001 to 9999 in column
A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to
select
in cells F1, F2 and F3. I current have to write
=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sumproduct((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one
single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Hi Peter,

Be careful that you do not double count your values.

The general "or" formula is IMHO
=SUMPRODUCT(SIGN((A1:A100=F1)+(A1:A100=F2)+(A1:A10 0=F3)),B1:B100)

Other formulas might appear more elegant but if F1=F2 or F2=F3 or
F1=F3 then they could easily double count entries.

Regards,
Bernd

Hi Peter,

your post is a good one...

when i try to use
=SUMPRODUCT((A1:A100=F1:F3)*B1:B100) ....then my result is #N/A

then when i try to re-arrange the search cells F1:F3 by placing them along
one row at F1:H1...
=SUMPRODUCT((A1:A100=F1:H1)*B1:B100)...I got the result same with other
combined function formulation...

If not so strict, i guess that in order to have a simple single sumproduct
formula, the data and search criteria need to be arranged as well..along with
the sumproduct operands.

regards
--
*****
birds of the same feather flock together..



"Peter" wrote:

Do you know how to include a 'is one of' argument to a sumproduct function?
For example if I have a list of codes in the range 0001 to 9999 in column A1
to A100 and the (say) $ amounts in B1 to B100 and the values I wish to select
in cells F1, F2 and F3. I current have to write
=(sumproduct(--(a1:a100=F1),b1.b100)+sumproduct((--(a1:a100=F2),b1.b100)+sumproduct((--(a1:a100=F3),b1.b100)).
I am sure there is an easier way to the include the F1, F2 & F3 in one single
sumproduct formula. Any ideas?
Many thanks
--
Peter
London, UK

Hello Bernd,

I take your point on double counting, although Peter's current formula will
also double-count. To avoid that and, especially for criteria ranges larger
than 3, to avoid very long formulas you could employ this

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A100,F1:F3,0)),B1:B100)



"Bernd" wrote:

Hi Peter,

Be careful that you do not double count your values.

The general "or" formula is IMHO
=SUMPRODUCT(SIGN((A1:A100=F1)+(A1:A100=F2)+(A1:A10 0=F3)),B1:B100)

Other formulas might appear more elegant but if F1=F2 or F2=F3 or
F1=F3 then they could easily double count entries.

Regards,
Bernd

Hello Bernd,

I take your point about double-counting although I note that Peter's current
formula would also do that. To avoid double-counting, and to avoid long
formulas when criteria range is larger than 3 cells possibly

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A100,F1:F3,0)),B1:B100)

"Bernd" wrote:

Hi Peter,

Be careful that you do not double count your values.

The general "or" formula is IMHO
=SUMPRODUCT(SIGN((A1:A100=F1)+(A1:A100=F2)+(A1:A10 0=F3)),B1:B100)

Other formulas might appear more elegant but if F1=F2 or F2=F3 or
F1=F3 then they could easily double count entries.

Regards,
Bernd

Hello,

Nice one.

Copied...

Have fun,
Bernd