Sumproduct problem

[User3340]

I am trying to use sumproduct to count the number of times a certain combination of database entries come up. Specifically, I want to count an entry if it has a certain date, then match that up with a client. Clients can have more than one entry per date, and I want to be able to keep a running total of how many entries they have per date. Am I using the wrong formula?

 

[dave3009]

No your using the right formula

Can you post some data and actual formulas please

 

[Aladin Akyurek]

I am trying to use sumproduct to count the number of times a certain combination of database entries come up. Specifically, I want to count an entry if it has a certain date, then match that up with a client. Clients can have more than one entry per date, and I want to be able to keep a running total of how many entries they have per date. Am I using the wrong formula?

=SUMPRODUCT(--(DateRange=Date),--(ClientRange=Client))

Date and Clients are cell references that house conditions which the relevant ranges must meet.

 

[User3340]

further sumproduct...

Here is the formula I am trying to use:
=SUMPRODUCT((A9='Hedge Records'!$B$2:$B$3)*('FX Data Sheet'!$E$5='Hedge Records'!$A$2:$A$3)*('Hedge Records'!$I$2:$I$3))

The above formula actually works, as the "Hedge Records" sheet only has two entries (in row 2 and 3). When I try to extend the array (eg from B3 to B1000, so I don't have to think about updating it), I get a #value error.

In the formula, A9 is a client's name and E5 is a date.

 

[Aladin Akyurek]

Re: further sumproduct...

Here is the formula I am trying to use:
=SUMPRODUCT((A9='Hedge Records'!$B$2:$B$3)*('FX Data Sheet'!$E$5='Hedge Records'!$A$2:$A$3)*('Hedge Records'!$I$2:$I$3))

The above formula actually works, as the "Hedge Records" sheet only has two entries (in row 2 and 3). When I try to extend the array (eg from B3 to B1000, so I don't have to think about updating it), I get a #value error.

In the formula, A9 is a client's name and E5 is a date.

=SUMPRODUCT(--(A9='Hedge Records'!$B$2:$B$3),--('FX Data Sheet'!$E$5='Hedge Records'!$A$2:$A$3),Hedge Records'!$I$2:$I$3)

 

[User3340]

Still not working

That still returns #vale error when I change it to include a larger array than is listed. I want to be able to include an enormous array that will be filled as entries are posted. Please help.

 

[Aladin Akyurek]

Re: Still not working

That still returns #vale error when I change it to include a larger array than is listed. I want to be able to include an enormous array that will be filled as entries are posted. Please help.

You can't use whole columns like B:B, unless on Excel 2007...

You can't get #VALUE! error, unless the relevant ranges themselves house such an error.