If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum with 2 criteria
I want to setup monthly summary sheets that uses data from another page i
have a formula that works however to make it work i have to select cells on the ther page. What format do dates need to be in, every time i try a date i think will make the formula work i get #NAME? =SUMPRODUCT(--(Jobs!C8:C1200="Grade C"),--(Jobs!G8:Jobs!G1200=Jobs!G869),--(Jobs!G8:Jobs!G1200=Jobs!G990),--(Jobs!I8:Jobs!I1200=1),Jobs!J8:Jobs!J1200) Where Jobs!G869 is the start date and Jobs!G990 is the finish date |
#2
|
|||
|
|||
Hi!
I'm not sure what you're asking? One thing about your formula.... You don't have to repeat the sheet name if the range referenced is contiguous: Jobs!G8:Jobs!G1200=Jobs!G869 Just use: Jobs!G8:G1200=Jobs!G869 That will at least shorten things a little and make it easier to read. Biff "Dave" wrote in message ... I want to setup monthly summary sheets that uses data from another page i have a formula that works however to make it work i have to select cells on the ther page. What format do dates need to be in, every time i try a date i think will make the formula work i get #NAME? =SUMPRODUCT(--(Jobs!C8:C1200="Grade C"),--(Jobs!G8:Jobs!G1200=Jobs!G869),--(Jobs!G8:Jobs!G1200=Jobs!G990),--(Jobs!I8:Jobs!I1200=1),Jobs!J8:Jobs!J1200) Where Jobs!G869 is the start date and Jobs!G990 is the finish date |
#3
|
|||
|
|||
May not understand but here is a shot
--(Jobs!G8:Jobs!G1200=--"2005-10-01"),--(Jobs!G8:Jobs!G1200=--"2005-10-31" ) -- HTH Bob Phillips "Dave" wrote in message ... I want to setup monthly summary sheets that uses data from another page i have a formula that works however to make it work i have to select cells on the ther page. What format do dates need to be in, every time i try a date i think will make the formula work i get #NAME? =SUMPRODUCT(--(Jobs!C8:C1200="Grade C"),--(Jobs!G8:Jobs!G1200=Jobs!G869),--(Jobs!G8:Jobs!G1200=Jobs!G990),--(J obs!I8:Jobs!I1200=1),Jobs!J8:Jobs!J1200) Where Jobs!G869 is the start date and Jobs!G990 is the finish date |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
criteria, checkboxes, and/or | bicyclops | Running & Setting Up Queries | 4 | September 13th, 2005 10:47 PM |
Complex query criteria - desperate appeal | Ted Allen | Running & Setting Up Queries | 5 | November 17th, 2004 06:14 PM |
Duplicating Excel's Autofilter functionality | rgrantz | Running & Setting Up Queries | 0 | November 3rd, 2004 11:06 PM |
problem with criteria for query | Rawley | Running & Setting Up Queries | 4 | October 22nd, 2004 11:26 PM |
DSUM Criteria and Excel Help | Earl Kiosterud | Worksheet Functions | 2 | April 30th, 2004 07:55 PM |