help with excel formula please :)

[keasha]

hello -hoping someone may be able to help please!

i have attached a file, i wish column u ( cell u10) to reflect the YTD figure based on the date at the top

so ie U10 should be the sum of july and august depending on the date range specified in U3 and U4

and so on - should automatically reflect the YTD calc depending on the date range

hope someone can help - many thanks

 

[Hui]

U7: =SUMIFS(G7:R7,$G$3:$R$3,">="&$U$3,$G$4:$R$4,"<="&$U$4)
Copy down

 

[keasha]

hi Hui- thankyou so much for responding so quickly

i must be doing something wrong, its only returning august, not sum of july and august ?

 

[rahulshewale1]

Hii @keasha

In U6:
=SUMPRODUCT(($G$2:$S$2>=DATEVALUE($U$2))*($G$3:$S$3<=DATEVALUE($U$3)),G6:S6)


Regard
Rahul shewale

 

[Hui]

The problem is that the YearStartDate and YearEndDate on the options worksheet were text not dates



If you change these to dates by typing the dates withoutthe = or " marks eg: 1/7/2017 etc the Sumifs formula works

 

[keasha]

hi all have attached again, but still no go ! sorry i should mention that this is a JEt report, so thats why it has an options tab HUI, its directly pulling from Microsoft NAV , but I have to customise some columns and rows (U)

 

[Hui]

I get this


I downloaded your file
Copied my original formula:
=SUMIFS(G7:R7,$G$3:$R$3,">="&$U$3,$G$4:$R$4,"<="&$U$4)
and pasted it into cell U7
Copied it down

Then recalculated, Press F9, because you have calculation set to Manual

See the revised file here:

 

[keasha]

You are just an absolute gem Hui, thankyou so much