Another Multiple Conditions Query

[Guest]

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!

 

[wjohnson]

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"

 

[Guest]

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?

 

[Rob van Gelder]

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

 

[Roger Govier]

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

 

[Guest]

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

 

[Biff]

Hi!

Try this:

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

Biff