sum with 2 criteria
 

With the below eqn do the dates have to be a cell reference or is it possible
to use a specific date?

One way

=SUMPRODUCT(--(A2:A50="Department"),--(B2:B50=D2),--(B2:B50<=E2),C2:C50)

where D2 hold the start date, E2 the end date and C2:C50 the amount/values
to sum

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave" wrote in message
...
I have an excell sheet that gets updated daily with submissions from
different departments, what i want to do is to use a formula to submission
by
the department and between two dates

You can use both, but it's bad practice to hardwire anything into your
formulas. Your variables should always be visible and easily editable, and
you can save yourself a lot of heartache by ensuring that they are. I tend
to flag input variables with a blue bold font to show that they have
dependencies on them, and also to highlight what/where they are. It
generally makes the sheet easier to read, and ensures the key variables
stand out instantly.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Dave" wrote in message
...
With the below eqn do the dates have to be a cell reference or is it
possible
to use a specific date?

One way

=SUMPRODUCT(--(A2:A50="Department"),--(B2:B50=D2),--(B2:B50<=E2),C2:C50)

where D2 hold the start date, E2 the end date and C2:C50 the amount/values
to sum

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave" wrote in message
...
I have an excell sheet that gets updated daily with submissions from
different departments, what i want to do is to use a formula to
submission
by
the department and between two dates

You can use both, but it's bad practice to hardwire anything into your
formulas. Your variables should always be visible and easily editable, and
you can save yourself a lot of heartache by ensuring that they are. I tend
to flag input variables with a blue bold font to show that they have
dependencies on them, and also to highlight what/where they are. It
generally makes the sheet easier to read, and ensures the key variables
stand out instantly.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"Dave" wrote in message
...
With the below eqn do the dates have to be a cell reference or is it
possible
to use a specific date?

One way

=SUMPRODUCT(--(A2:A50="Department"),--(B2:B50=D2),--(B2:B50<=E2),C2:C50)

where D2 hold the start date, E2 the end date and C2:C50 the amount/values
to sum

--
Regards,

Peo Sjoblom

(No private emails please)


"Dave" wrote in message
...
I have an excell sheet that gets updated daily with submissions from
different departments, what i want to do is to use a formula to
submission
by
the department and between two dates