looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each customer for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04

Personally, I would use SUMPRODUCT for this as it allows functions to be
performed on the range you are checking. For your setup, I have assumed the
following:

Customer Numbers in colA Sheet1
Dates in colB on sheet1
Sales in ColC on sheet1
data from row 2 to row 1000 (headers in row 1)

In sheet 2, customer number in A2 and "Jan" in B1, "Feb" in C1 etc

In B2, enter:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(MONTH(Sheet1 !$B$2:$B$1000)=B$1)*(Sheet1!$C$2:$C$1000))

and copy across / down

HTH
Rgds
Geoff

"D@annyBoy" wrote:

looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each customer for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04

Hi Geoff - Is it just me or is TT down and out for the count at the moment?

Regards
Ken.................

"xlbo" wrote:

Personally, I would use SUMPRODUCT for this as it allows functions to be
performed on the range you are checking. For your setup, I have assumed the
following:

Customer Numbers in colA Sheet1
Dates in colB on sheet1
Sales in ColC on sheet1
data from row 2 to row 1000 (headers in row 1)

In sheet 2, customer number in A2 and "Jan" in B1, "Feb" in C1 etc

In B2, enter:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(MONTH(Sheet1 !$B$2:$B$1000)=B$1)*(Sheet1!$C$2:$C$1000))

and copy across / down

HTH
Rgds
Geoff

"D@annyBoy" wrote:

looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each customer for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04

I am a little confused

in sheet A I have the following

DATE Customer Name LC AMOUNT
01-01-04 Mr A USD36,437.20

15-02-04 Mr B USD25,090

in sheet B I have the following

CUSTOMER JAN
FEB MAR
Mr A 36,437.20

Mr B
25,090

this is what I am trying to achieve



"xlbo" wrote in message
...
Personally, I would use SUMPRODUCT for this as it allows functions to be
performed on the range you are checking. For your setup, I have assumed
the
following:

Customer Numbers in colA Sheet1
Dates in colB on sheet1
Sales in ColC on sheet1
data from row 2 to row 1000 (headers in row 1)

In sheet 2, customer number in A2 and "Jan" in B1, "Feb" in C1 etc

In B2, enter:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(MONTH(Sheet1 !$B$2:$B$1000)=B$1)*(Sheet1!$C$2:$C$1000))

and copy across / down

HTH
Rgds
Geoff

"D@annyBoy" wrote:

looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each customer
for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04

Ken - yes - hence my appearance here - gotta get my fix somewhere !

D@annyBoy - what are you confused about ??
I have given you the formula that will do as you require. The only change
seems to be that you have customer in colB and Date in ColA rather than the
other way round. Just realised however that I cannot use "Month" so slight
amendment needed:

Hence, the formula would be:
=SUMPRODUCT((Sheet1!$B$2:$B$1000=A2)*(TEXT(Sheet1! $A$2:$A$1000),"mmm")=B$1)*(Sheet1!$C$2:$C$1000))

This will sum all the values in Sheet1, columnC where the customer number in
the range B2:B1000 is equal to the customer entered in cell A2 AND where the
MONTH of the date in the range A2:A1000 is equal to the 3 letter abbreviated
month in your header row....

Just sub the sheet name in and chenge the 1000 to be correct for your setup

Alternatively, you can just use a pivot table and GROUP the dates as months...

HTH
Rgds
Geoff

"D@annyBoy" wrote:

I am a little confused

in sheet A I have the following

DATE Customer Name LC AMOUNT
01-01-04 Mr A USD36,437.20

15-02-04 Mr B USD25,090

in sheet B I have the following

CUSTOMER JAN
FEB MAR
Mr A 36,437.20

Mr B
25,090

this is what I am trying to achieve



"xlbo" wrote in message
...
Personally, I would use SUMPRODUCT for this as it allows functions to be
performed on the range you are checking. For your setup, I have assumed
the
following:

Customer Numbers in colA Sheet1
Dates in colB on sheet1
Sales in ColC on sheet1
data from row 2 to row 1000 (headers in row 1)

In sheet 2, customer number in A2 and "Jan" in B1, "Feb" in C1 etc

In B2, enter:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(MONTH(Sheet1 !$B$2:$B$1000)=B$1)*(Sheet1!$C$2:$C$1000))

and copy across / down

HTH
Rgds
Geoff

"D@annyBoy" wrote:

looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each customer
for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04

managed to solve the problem via trial and error :-)


"xlbo" wrote in message
...
Ken - yes - hence my appearance here - gotta get my fix somewhere !

D@annyBoy - what are you confused about ??
I have given you the formula that will do as you require. The only change
seems to be that you have customer in colB and Date in ColA rather than
the
other way round. Just realised however that I cannot use "Month" so slight
amendment needed:

Hence, the formula would be:
=SUMPRODUCT((Sheet1!$B$2:$B$1000=A2)*(TEXT(Sheet1! $A$2:$A$1000),"mmm")=B$1)*(Sheet1!$C$2:$C$1000))

This will sum all the values in Sheet1, columnC where the customer number
in
the range B2:B1000 is equal to the customer entered in cell A2 AND where
the
MONTH of the date in the range A2:A1000 is equal to the 3 letter
abbreviated
month in your header row....

Just sub the sheet name in and chenge the 1000 to be correct for your
setup

Alternatively, you can just use a pivot table and GROUP the dates as
months...

HTH
Rgds
Geoff

"D@annyBoy" wrote:

I am a little confused

in sheet A I have the following

DATE Customer Name LC AMOUNT
01-01-04 Mr A USD36,437.20

15-02-04 Mr B USD25,090

in sheet B I have the following

CUSTOMER JAN
FEB MAR
Mr A 36,437.20

Mr B
25,090

this is what I am trying to achieve



"xlbo" wrote in message
...
Personally, I would use SUMPRODUCT for this as it allows functions to
be
performed on the range you are checking. For your setup, I have assumed
the
following:

Customer Numbers in colA Sheet1
Dates in colB on sheet1
Sales in ColC on sheet1
data from row 2 to row 1000 (headers in row 1)

In sheet 2, customer number in A2 and "Jan" in B1, "Feb" in C1 etc

In B2, enter:
=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(MONTH(Sheet1 !$B$2:$B$1000)=B$1)*(Sheet1!$C$2:$C$1000))

and copy across / down

HTH
Rgds
Geoff

"D@annyBoy" wrote:

looking for tips on how to accomplish the following


I have 10 customers who buy goods from me every month

In worksheet A, I input dates and sales value.

customer a

date sales
01/jan/04 500.00
15/jan/04 200.00

4/feb/04 300.00
28/feb/04 450.00


in worksheet B I want to use functions to query sales from each
customer
for
each month

for example;


jan feb etc
customer a 700 750

I am trying to figure out how to extract the sales from worksheet A
and
display the total monthly sales in worksheet B.

SUMIF(range,criteria,sum_range)

How do I set the criteria to months

date input is d/m/y

can I use 1/1/04 but <31/1/04

followed by 1/2/04 but <29/2/04