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

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

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