Counting Days in a String

[dscheeringa]

Excel 2003. Trying to count the days within a search string. Enter a start date in F15 and an end date in G15. As it stands, the current formula in I17 thru I24 is only counting the days of consumers who started their stay within the search string not any days that occurred during the string if the start date began prior the date as loaded into F15. Case in point, in the Consumers Sheet, Row 37, the 10 days stayed in September are not counted within the search string. Cell I20 on the intake sheet should be showing 10 days. Any Ideas?

Dan

[benishiryo]

hi dscheeringa. your SUMPRODUCT is looking at column F of "Consumers" worksheet & according to the From & To dates, they must within 1 Sep 12 to 30 Sep 12. but the Row 37 (i think you are referring to row 38 instead) is in 24 Aug 12.

not sure if i got what you trying to do. so if row 38 is 24 Aug to 10 Sep, you want to add them because 1 Sep to 10 Sep is within your criteria? then you can try summing twice:

Please Login or Register  to view this content.

i simply added a code like yours but looking at the Discharge Date in the 2nd one.

if you really wanted to look at those only within 1 sep to 30 sep, then you gotta change your range to Column G in the 2nd criteria:

Please Login or Register  to view this content.

[NBVC]

Try this...

add a helper column to Consumers, I2:

Please Login or Register  to view this content.

copied down

then formula in I17 of Intakes sheet is:

=SUMIF(Consumers!$D$2:$D$997,$B17,Consumers!$I$2:$I$997)

copied down

You would need to adjust the other formulas in the table, likewise to refer to column I.... as per attached.

e,g. C17 becomes:

=SUMPRODUCT(--(Consumers!$D$2:$D$997=$B17),--(Consumers!$I$2:$I$997>0))

[dscheeringa]

NBVC your formula fits the bill except for cell I30 (and I have been going nuts trying to fix that). Start date and end date are the same and will show as "0". If the two dates are the same, it still needs to count as "1". Basically, "Check in and out on the same day, you will still be billed for 1 day."