Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
D@annyBoy
 
Posts: n/a
Default 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   Report Post  
xlbo
 
Posts: n/a
Default

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



  #3   Report Post  
D@annyBoy
 
Posts: n/a
Default

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





  #4   Report Post  
Ken Wright
 
Posts: n/a
Default

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



  #5   Report Post  
xlbo
 
Posts: n/a
Default

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








  #6   Report Post  
D@annyBoy
 
Posts: n/a
Default

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF with only one criteria cell within range Stan Excel Discussion (Misc queries) 5 December 16th 04 11:12 PM
How do I ask for multiple criteria when creating a "sumif" formul. Rachelle Excel Worksheet Functions 3 December 2nd 04 12:49 AM
SUMIF multiple criteria in 1 range Mike@Q Excel Worksheet Functions 5 November 26th 04 04:55 PM
SUMIF across a range of worksheets Mike@Q Excel Worksheet Functions 3 November 24th 04 03:36 PM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 08:52 PM


All times are GMT +1. The time now is 10:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"