SUMPRODUCT QUESTION

[Guest]

I am trying to write a formula that checks whether certain numbers are
present in a column of many numbers.

If these specific numbers are present, then there total should be
calculated. I have experimented with both the SUMIF and SUMPRODUCT formulae -
but to no avail.

so far i have;
=SUMPRODUCT(((I2:I24=H2)+((I2:I24=H3)+((I2:I24=H4)+(I2:I24=H13)))))

The colum to be checked is I2:I24, and the numbers in this column I am
looking for are H2, H3, H4, H13. (this is simplified - in reality there will
be many more)

Many thanks for your help

 

[Don Guillett]

h2 is not a number so you must mean the number in h2. try

=SUMPRODUCT((I2:I24={H2,H3,H4,H13})*1)
untested

 

[Guest]

It isn't pretty, but I think it does what you are trying to do:

=SUM(COUNTIF(I2:I24,H2)*H2,COUNTIF(I2:I24,H3)*H3,COUNTIF(I2:I24,H4)*H4,COUNTIF(I2:I24,H13)*H13)

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(I2:I24,H2:H4))+SUMIF(I2:I24,H13)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)