Another Multiple Conditions Query

[D Zandveld]

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"

[D Zandveld]

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

[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

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


"D Zandveld" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[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
--
Regards

Roger Govier


"D Zandveld" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

[D Zandveld]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

[Biff]

Hi!

Try this:

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

Biff

"D Zandveld" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>> >
>>
>>
>>