Remember Me?

#1
 Posts: n/a
can I use a range of dates as a criteria when using sumif?

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

#2
 xlbo Posts: n/a

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

"[email protected]" 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

#3
 Posts: n/a

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

"[email protected]" 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

#4
 Ken Wright Posts: n/a

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

"[email protected]" 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

#5
 xlbo Posts: n/a

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

[email protected] - 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

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

"[email protected]" 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

"[email protected]" 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

#6
 Posts: n/a

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

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

[email protected] - 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

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

"[email protected]" 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

"[email protected]" 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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Stan Excel Discussion (Misc queries) 5 December 16th 04 10:12 PM Rachelle Excel Worksheet Functions 3 December 1st 04 11:49 PM [email protected] Excel Worksheet Functions 5 November 26th 04 03:55 PM [email protected] Excel Worksheet Functions 3 November 24th 04 02:36 PM Number Cruncher Excel Worksheet Functions 2 November 4th 04 07:52 PM

All times are GMT +1. The time now is 03:49 AM.