Sumproduct Help Required
 

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

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

I want to find out, for a given ID in cell A1, both the number of
values which exceed a given value in B1, and the aggregate of those
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 relevant
values. I was kind of thinking ..

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

Can anyone help me please ? :)


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=345976

Hi Timmy

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

should give you what you're after

Cheers
JulieD

"Timmy Mac1" wrote in message
...

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

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

I want to find out, for a given ID in cell A1, both the number of
values which exceed a given value in B1, and the aggregate of those
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 relevant
values. I was kind of thinking ..

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

Can anyone help me please ? :)


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=345976

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 :confused: )

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



JulieD Wrote:
Hi Timmy

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

should give you what you're after

Cheers
JulieD

"Timmy Mac1" wrote in
message
...

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

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

I want to find out, for a given ID in cell A1, both the number of
values which exceed a given value in B1, and the aggregate of those
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 relevant
values. I was kind of thinking ..

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

Can anyone help me please ? :)


--
Timmy Mac1

------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread:
http://www.excelforum.com/showthread...hreadid=345976



--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=345976

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)


"Timmy Mac1" wrote in message
...

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

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

I want to find out, for a given ID in cell A1, both the number of
values which exceed a given value in B1, and the aggregate of those
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 relevant
values. I was kind of thinking ..

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

Can anyone help me please ? :)


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=345976