Multiple critera with in a sumif statement

[Mark Allen]

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

 

[Bernie Deitrick]

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]

Hi Bernie,

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

Any idea's ???

Mark

 

[Bernie Deitrick]

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]

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

 

[Mark Allen]

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