In layman's terms, I am trying to perform the following:

Assume:
- Column A contains a list of Account numbers (that may contain duplicates)
- Column B contains a list of Dates (that may contain duplicates)
- Column C contains a list of Amounts

I need to SUM the vales that meet both the criteria, and return them to
another cell.

So for example:
Column A Contains: 4444, 5678, 5678, 1234, 4444
Column B Contains: 14-Jan-06, 23-Jan-06, 23-Jan-06, 01-Feb-06, 14-Jan-06
Column C Contains: $100, $300, $500, $250, $130

So, under the heading January 06, the correct values to return would be:
$230 for account 4444
$800 for account 5678
and account 1234 would return $0 as it is outside the month of January

I am familiar with MONTH, DATEVALUE, SUMPRODUCT, SUM, SUMIF, VLOOKUP,
HLOOKUP, LOOKUP etc. but this problem is driving me nuts...

Appreciate any assistance from you experts out there...Thanks!

I would try using the PIVOT Table function - Located under Menu Item
"DATA"
For the PIVOT TABLE select all of Column A, B, C.
For your Rows Drag and Drop Column A
For your Columns - Drag and Drop Column B
For your "Data" field - drag and Drop Column C
And it will be done.
Then if you need to work with the DATA Copy the PIVOT TABLE and then do
a do a PASTE - SPECIAL - and select "VALUES"


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=508814

Thanks, the problem is it is a dynamic array, which will grow over the year.
The data resides in a separate file located on the server, updated by our
accounts department.

I really need a flat combination of SUMPRODUCT et. al.

I have done it before (the last time I was writing a sales system), that's
the frustrating part...appreciate the idea though.

Any other suggestions?

"wjohnson" wrote:


I would try using the PIVOT Table function - Located under Menu Item
"DATA"
For the PIVOT TABLE select all of Column A, B, C.
For your Rows Drag and Drop Column A
For your Columns - Drag and Drop Column B
For your "Data" field - drag and Drop Column C
And it will be done.
Then if you need to work with the DATA Copy the PIVOT TABLE and then do
a do a PASTE - SPECIAL - and select "VALUES"


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=508814

B7 = 1-Jan-2006 (formatted as Jan-06)
A8 = 4444
B8 = =SUMPRODUCT(($A$1:$A$5 = $A8) * (TEXT($B$1:$B$5, "yyyymm") = TEXT(B$7,
"yyyymm")), $C$1:$C$5)

There are other ways to do the date range checks.. this is one way.

Google: xldynamic sumproduct

--
Rob van Gelder - http://www.vangelder.co.nz/


"D Zandveld" wrote in message
...
In layman's terms, I am trying to perform the following:

Assume:
- Column A contains a list of Account numbers (that may contain
duplicates)
- Column B contains a list of Dates (that may contain duplicates)
- Column C contains a list of Amounts

I need to SUM the vales that meet both the criteria, and return them to
another cell.

So for example:
Column A Contains: 4444, 5678, 5678, 1234, 4444
Column B Contains: 14-Jan-06, 23-Jan-06, 23-Jan-06, 01-Feb-06, 14-Jan-06
Column C Contains: $100, $300, $500, $250, $130

So, under the heading January 06, the correct values to return would be:
$230 for account 4444
$800 for account 5678
and account 1234 would return $0 as it is outside the month of January

I am familiar with MONTH, DATEVALUE, SUMPRODUCT, SUM, SUMIF, VLOOKUP,
HLOOKUP, LOOKUP etc. but this problem is driving me nuts...

Appreciate any assistance from you experts out there...Thanks!

Hi

The fact that the data is dynamic and growing over the year doesn't
matter.
In the Pivot table source data filed, give it a named range, e.g.
Mydata.
Make Mydata a dynamic range. for more information on setting Dynamic
Ranges and Pivot Tables take a look at Debra Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll down to Pivot Tables
--
Regards

Roger Govier


"D Zandveld" wrote in message
...
Thanks, the problem is it is a dynamic array, which will grow over the
year.
The data resides in a separate file located on the server, updated by
our
accounts department.

I really need a flat combination of SUMPRODUCT et. al.

I have done it before (the last time I was writing a sales system),
that's
the frustrating part...appreciate the idea though.

Any other suggestions?

"wjohnson" wrote:


I would try using the PIVOT Table function - Located under Menu Item
"DATA"
For the PIVOT TABLE select all of Column A, B, C.
For your Rows Drag and Drop Column A
For your Columns - Drag and Drop Column B
For your "Data" field - drag and Drop Column C
And it will be done.
Then if you need to work with the DATA Copy the PIVOT TABLE and then
do
a do a PASTE - SPECIAL - and select "VALUES"


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile:
http://www.excelforum.com/member.php...o&userid=29640
View this thread:
http://www.excelforum.com/showthread...hreadid=508814

Hi again

I've managed to get it to return a correct result querying a date range and
summing the values using:

=SUMPRODUCT(--(Data!C1:C88=Dates!G2),--(Data!C1:C88<=Dates!H2),Data!H1:H88)

Now, all I need to do is build the addiitonal query for the account number...

In theory, it should be:

=IF($A$1:$A$100=<Static Account Number Reference,
SUMPRODUCT(--(Data!C1:C100=Dates!G2),--(Data!C1:C100<=Dates!H2),Data!H1:H100),"")

But this doesn't necessarily work in practice - any clues? Thanks




"Rob van Gelder" wrote:

B7 = 1-Jan-2006 (formatted as Jan-06)
A8 = 4444
B8 = =SUMPRODUCT(($A$1:$A$5 = $A8) * (TEXT($B$1:$B$5, "yyyymm") = TEXT(B$7,
"yyyymm")), $C$1:$C$5)

There are other ways to do the date range checks.. this is one way.

Google: xldynamic sumproduct

--
Rob van Gelder - http://www.vangelder.co.nz/


"D Zandveld" wrote in message
...
In layman's terms, I am trying to perform the following:

Assume:
- Column A contains a list of Account numbers (that may contain
duplicates)
- Column B contains a list of Dates (that may contain duplicates)
- Column C contains a list of Amounts

I need to SUM the vales that meet both the criteria, and return them to
another cell.

So for example:
Column A Contains: 4444, 5678, 5678, 1234, 4444
Column B Contains: 14-Jan-06, 23-Jan-06, 23-Jan-06, 01-Feb-06, 14-Jan-06
Column C Contains: $100, $300, $500, $250, $130

So, under the heading January 06, the correct values to return would be:
$230 for account 4444
$800 for account 5678
and account 1234 would return $0 as it is outside the month of January

I am familiar with MONTH, DATEVALUE, SUMPRODUCT, SUM, SUMIF, VLOOKUP,
HLOOKUP, LOOKUP etc. but this problem is driving me nuts...

Appreciate any assistance from you experts out there...Thanks!

Hi!

Try this:

=SUMPRODUCT(--(A1:A100=act_num),--(Data!C1:C100=Dates!G2),--(Data!C1:C100<=Dates!H2),Data!H1:H100)

Biff

"D Zandveld" wrote in message
...
Hi again

I've managed to get it to return a correct result querying a date range
and
summing the values using:

=SUMPRODUCT(--(Data!C1:C88=Dates!G2),--(Data!C1:C88<=Dates!H2),Data!H1:H88)

Now, all I need to do is build the addiitonal query for the account
number...

In theory, it should be:

=IF($A$1:$A$100=<Static Account Number Reference,
SUMPRODUCT(--(Data!C1:C100=Dates!G2),--(Data!C1:C100<=Dates!H2),Data!H1:H100),"")

But this doesn't necessarily work in practice - any clues? Thanks




"Rob van Gelder" wrote:

B7 = 1-Jan-2006 (formatted as Jan-06)
A8 = 4444
B8 = =SUMPRODUCT(($A$1:$A$5 = $A8) * (TEXT($B$1:$B$5, "yyyymm") =
TEXT(B$7,
"yyyymm")), $C$1:$C$5)

There are other ways to do the date range checks.. this is one way.

Google: xldynamic sumproduct

--
Rob van Gelder - http://www.vangelder.co.nz/


"D Zandveld" wrote in message
...
In layman's terms, I am trying to perform the following:

Assume:
- Column A contains a list of Account numbers (that may contain
duplicates)
- Column B contains a list of Dates (that may contain duplicates)
- Column C contains a list of Amounts

I need to SUM the vales that meet both the criteria, and return them to
another cell.

So for example:
Column A Contains: 4444, 5678, 5678, 1234, 4444
Column B Contains: 14-Jan-06, 23-Jan-06, 23-Jan-06, 01-Feb-06,
14-Jan-06
Column C Contains: $100, $300, $500, $250, $130

So, under the heading January 06, the correct values to return would
be:
$230 for account 4444
$800 for account 5678
and account 1234 would return $0 as it is outside the month of January

I am familiar with MONTH, DATEVALUE, SUMPRODUCT, SUM, SUMIF, VLOOKUP,
HLOOKUP, LOOKUP etc. but this problem is driving me nuts...

Appreciate any assistance from you experts out there...Thanks!