Sumproduct function
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
|