Sumproduct problem

User3340

New Member
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?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dave3009

Well-known Member
No your using the right formula

Can you post some data and actual formulas please

MrExcel MVP
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

New Member
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.

MrExcel MVP
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

New Member
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.

MrExcel MVP
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.

Replies
1
Views
484
Replies
15
Views
927
Replies
1
Views
171
Replies
9
Views
200
Replies
2
Views
98

1,191,040
Messages
5,984,295
Members
439,882
Latest member
gerdc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back