Sum based on condtions in another worksheet

[wpryan]

Hi All, I am working a staffing calculator that will project staffing needs based on various criteria. One of them will be to sum days worked for conferences. The user should enter a date range of the conference and a formula will sum those days - this is easy enough. However, what I want to do is to create a formula that will look for a date range within the cells, and then sum those total days for that date range. For example, rows 2 and 5 are within the date range of March. The rest are not. How can I get the formula to sum only the cells for the date range in question? Will I have to use VBA? I tried working with the following formula, but obviously it will sum all days... Thanks in advance.

=IF(ISBLANK('2010 Conferences'!B:C),"",IF(OR('2010 Conferences'!B:B>1/7/2011,'2010 Conferences'!B:B<31/7/2011),SUM('2010 Conferences'!E:E),""))

 

[VoG]

Try

=SUMPRODUCT(--('2010 Conferences'!B1:B100>="1/7/2011"+0),--('2010 Conferences'!B1:B100<="31/7/2011"+0),'2010 Conferences'!E1:E100)

 

[wpryan]

Thanks for your reply. it works great. Can you explain to me what the "--" means in the equation?

 

[VoG]

('2010 Conferences'!B1:B100>="1/7/2011"+0 returns an array of False/True. The -- operator converts those to 0/1 that SUMPRODUCT can work with.

 

[wpryan]

Thanks again!

 

[T. Valko]

Thanks for your reply. it works great. Can you explain to me what the "--" means in the equation?

You might be interested in this...

http://xldynamic.com/source/xld.SUMPRODUCT.html