microsoft excelworksheet-function
I have a spreadsheet which is similar in structure to what you can see on this spreadsheet.
I tried several ways to calculate YTD figures of the different categories but failed. What formula should I put in cell C14 to get all fruits summed up between Week 1 and Week 5?
Try this solution using SUM & IF in an Array Formula. Sample data is in cells A2:I9. Date format is mm/dd/yyyy in my Excel. C12, C13 & C14 hold Type, From and To dates respectively. Total is displayed in F12.
In F12 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an array formula. The formula shall now be enclosed in curly braces to indicate that it's an array formula. This step is important, the formula shall only work correctly if it's an Array Formula.
=SUM(IF($A$3:$A$9=C12,IF($B$2:$I$2<=C14,IF($B$2:$I$2>=C13,$B$3:$I$9,0))))
Excel uses floating point math, that means sometimes there are such digits hidden in your values. (https://docs.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result)
Those are generally displayed correctly, however when you convert them to text, those indeed may cause issues, one workaround is to use ROUND in your formula:
="+ £"& ROUND(-'Sue''s Outgoings'!C13,2) &" Shortfall"
Best Answer
Apparently I can't add a comment, but Scott Craner's answer only gives the total for week 5, not a YTD. To add the totals for each week up to and including week 5, change the first equal sign to <=: