Formula issue...

[apatreau]

Hi there,

I have built a budget tracker spreadsheet. Currently the below formula is pulling in TOO MUCH ranged data. I believe with the bolded portion below that I have asked it to take F17 (the date + 6 days) worth of information from my timecard worksheet...but it keeps taking the information for what seems like F17+7...when I try to make ANY changes to this formula, even if I change the +6 to again a +6 the total returned number ends up at zero...which is wrong.

I have never encountered something like this, any ideas?

=SUM(IF(TimeCards!$B$5:$B$300=SummaryAndForecast!$B20,IF(TimeCards!$D$5:$D$300=SummaryAndForecast!$E20,IF(TimeCards!$A$5:$A$300>=SummaryAndForecast!F$17,IF(TimeCards!$A$5:$A$300<=SummaryAndForecast!F$17+6,TimeCards!$C$5:$C$300)))))

 

[hallingh]

I'm assuming that you're remembering to enter this formula using ctrl + shift + enter? Sometimes when my formulas don't work it's something simple like this that I am absent-mindedly forgetting.

Hank

 

[shg]

Perhaps instead,

=SUMPRODUCT(
(TimeCards!$B$5:$B$300 = SummaryAndForecast!$B20) *
(TimeCards!$D$5:$D$300 = SummaryAndForecast!$E20) *
(TimeCards!$A$5:$A$300 >= SummaryAndForecast!F$17) *
(TimeCards!$A$5:$A$300 <= SummaryAndForecast!F$17+6), TimeCards!$C$5:$C$300)

 

[apatreau]

I'm assuming that you're remembering to enter this formula using ctrl + shift + enter? Sometimes when my formulas don't work it's something simple like this that I am absent-mindedly forgetting.

Hank

Yep, that was it! Somehow I've been using excel for years and years and have only twice needed to do this. Thanks!