sum with 2 criteria

[Guest]

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)

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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
------------------------------­------------------------------­----------------