Sumproduct Help Required

[Timmy Mac1]

Tried this yesterday and have only just realised that my message didn'
actually load up.

I have a column range of IDs named ALPHA alongside a column range o
values named BETA.

I want to find out, for a given ID in cell A1, both the number o
values which exceed a given value in B1, and the aggregate of thos
values.

I can get the number of values by the following formula....

=SUMPRODUCT((ALPHA=A1)*(BETA>=B1))

However I'm struggling to get an aggregate total of the relevan
values. I was kind of thinking ..

=SUMPRODUCT((ALPHA=A1)*(BETA>=B1)*(BETA)) would do the trick but i
doesn't , I only get a return of #VALUE

Can anyone help me please ? :

 

[JulieD]

Hi Timmy

=SUMPRODUCT(--(alpha=A1),--(beta>=B1)*beta)

should give you what you're after

Cheers
JulieD

 

[Timmy Mac1]

Julie

Many many thanks. I've got it to work with a slight change to what you
had..

=SUMPRODUCT((ALPHA=A1)*(BETA>=B1),(BETA))

(sorry I wasn't sure what the -- represents )

It's really bugged me this thing so once again thanks for taking the
time to respond and getting me in the right direction

 

[Bob Phillips]

Timmy,

Your formula works fine for me.

The only thing I can think is that you have a text string in one of the
cells in BETA.

--

HTH

RP
(remove nothere from the email address if mailing direct)