| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with sumif formula with multiple critera | Excel Worksheet Functions | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
Linear Mode
