Multiple critera with in a sumif statement
 

Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards

Multiple critera with in a sumif statement
 

Mark,

You need to specify in which column you are looking for the value in cell 'Mnths Tracking'!C3 (I'm
assuming G), but you could use something like this:

=SUMPRODUCT(('MTD Orders'!$G1:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B1:$B9993=DATEVALUE("1/1/08"))*('MTD Orders'!$B1:$B9993<=DATEVALUE("7/1/08"))*'MTD
Orders'!$L1:$L9993)


HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards

Multiple critera with in a sumif statement
 

Hi Bernie,

thatnks for your reply but I get #REF! and I cant work out why ??

Any idea's ???

Mark

"Bernie Deitrick" wrote:

Mark,

You need to specify in which column you are looking for the value in cell 'Mnths Tracking'!C3 (I'm
assuming G), but you could use something like this:

=SUMPRODUCT(('MTD Orders'!$G1:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B1:$B9993=DATEVALUE("1/1/08"))*('MTD Orders'!$B1:$B9993<=DATEVALUE("7/1/08"))*'MTD
Orders'!$L1:$L9993)


HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards

Multiple critera with in a sumif statement
 

Mark,

Make sure that you take out the line breaks, and get the spacing and spelling of the sheet names
correct. Are the sheets both in the same workbook as the sheet/cell with the formula?

Other thing to try include:

Do you have headers in row 1? Remove that from the comparison: 'MTD Orders'!$G1:$G9993 becomes 'MTD
Orders'!$G2:$G9993, etc. That is especially important for dates.

Are your dates actual dates, or are they strings that look like dates? Does chaning the format on
those cells change how they are displayed? - if so, they are actual dates. ( They need to be actual
dates for the comparison to work.)

I tested the formula and it worked fine for me....

HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Hi Bernie,

thatnks for your reply but I get #REF! and I cant work out why ??

Any idea's ???

Mark

"Bernie Deitrick" wrote:

Mark,

You need to specify in which column you are looking for the value in cell 'Mnths Tracking'!C3
(I'm
assuming G), but you could use something like this:

=SUMPRODUCT(('MTD Orders'!$G1:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B1:$B9993=DATEVALUE("1/1/08"))*('MTD Orders'!$B1:$B9993<=DATEVALUE("7/1/08"))*'MTD
Orders'!$L1:$L9993)


HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards

Multiple critera with in a sumif statement
 

OK, I have now made G1, G2 etc for header row.
Corrected the line spacing etc in the formula.
The sheets are all in the same work book.

Positive new I now don't get #REF! I just get a 0 dollar value....so close...

=SUMPRODUCT(('MTD Orders'!$G2:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B2:$B9993=DATEVALUE("10/1/2008"))*('MTD
Orders'!$B2:$B9993<=DATEVALUE("10/30/2008"))*'MTD Orders'!$L2:$L9993)

Any other ideas ?

Regards

Mark

"Bernie Deitrick" wrote:

Mark,

Make sure that you take out the line breaks, and get the spacing and spelling of the sheet names
correct. Are the sheets both in the same workbook as the sheet/cell with the formula?

Other thing to try include:

Do you have headers in row 1? Remove that from the comparison: 'MTD Orders'!$G1:$G9993 becomes 'MTD
Orders'!$G2:$G9993, etc. That is especially important for dates.

Are your dates actual dates, or are they strings that look like dates? Does chaning the format on
those cells change how they are displayed? - if so, they are actual dates. ( They need to be actual
dates for the comparison to work.)

I tested the formula and it worked fine for me....

HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Hi Bernie,

thatnks for your reply but I get #REF! and I cant work out why ??

Any idea's ???

Mark

"Bernie Deitrick" wrote:

Mark,

You need to specify in which column you are looking for the value in cell 'Mnths Tracking'!C3
(I'm
assuming G), but you could use something like this:

=SUMPRODUCT(('MTD Orders'!$G1:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B1:$B9993=DATEVALUE("1/1/08"))*('MTD Orders'!$B1:$B9993<=DATEVALUE("7/1/08"))*'MTD
Orders'!$L1:$L9993)


HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards

Multiple critera with in a sumif statement
 

Not dure if the information elow helps or not, it is the first 5 lines from
the Excel data on the worksheet MTD Orders ??

Order date Invoice Date Name City Postal code Territory
Code Srep GP Total Sales tax-1 amount Sales tax-2 amount Total Net % Marg
10/16/2008 10/17/2008 ANO
ARCHITECTS/ARCHITECTES.INC. Timmins P4N1A9 TIMMINS 150 138.34 651.34 28.82 46.11 576.41 0.24
10/16/2008 10/17/2008 ANO
ARCHITECTS/ARCHITECTES.INC. Timmins P4N1A9 TIMMINS 150 65 320.52 14.18 22.69 283.65 0.23
10/21/2008 10/21/2008 ANO
ARCHITECTS/ARCHITECTES.INC. Timmins P4N1A9 TIMMINS 150 41.19 154.91 6.85 10.97 137.09 0.30
10/16/2008 10/24/2008 ANO
ARCHITECTS/ARCHITECTES.INC. Timmins P4N1A9 TIMMINS 150 5.69 18.67 0.83 1.32 16.52 0.34
10/10/2008 10/17/2008 CANADIAN RED CROSS\CHS TIMMINS P4N
2S7 TIMMINS 150 54.02 198.28 8.77 14.04 175.47 0.31


"Bernie Deitrick" wrote:

Mark,

Make sure that you take out the line breaks, and get the spacing and spelling of the sheet names
correct. Are the sheets both in the same workbook as the sheet/cell with the formula?

Other thing to try include:

Do you have headers in row 1? Remove that from the comparison: 'MTD Orders'!$G1:$G9993 becomes 'MTD
Orders'!$G2:$G9993, etc. That is especially important for dates.

Are your dates actual dates, or are they strings that look like dates? Does chaning the format on
those cells change how they are displayed? - if so, they are actual dates. ( They need to be actual
dates for the comparison to work.)

I tested the formula and it worked fine for me....

HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Hi Bernie,

thatnks for your reply but I get #REF! and I cant work out why ??

Any idea's ???

Mark

"Bernie Deitrick" wrote:

Mark,

You need to specify in which column you are looking for the value in cell 'Mnths Tracking'!C3
(I'm
assuming G), but you could use something like this:

=SUMPRODUCT(('MTD Orders'!$G1:$G9993='Mnths Tracking'!C3)*('MTD
Orders'!$B1:$B9993=DATEVALUE("1/1/08"))*('MTD Orders'!$B1:$B9993<=DATEVALUE("7/1/08"))*'MTD
Orders'!$L1:$L9993)


HTH,
Bernie
MS Excel MVP


"Mark Allen" wrote in message
...
Is it possible to use multiple critera for a sum if statement ?

This is what I have so far:
=SUMIF('MTD Orders'!$G:$M,'Mnths Tracking'!C3,'MTD Orders'!$L1:$L9993)

I also want to specifiy a date range from column B ie. from 1/1/08 to 7/1/08.

Can anyone help please ?

Regards