sum up all cells between dates that is a text

2
votes

I have 2 tables. In first table I have project name and the cost for each project. The project names always start with data (yyyymmdd) for instance 20171201_Project1, 20171202_Project_2 etc. In second table I have dates and Summary of language 1

What I would like to do is to sum up all projects in Sheet2 between 2 dates so if I have done 7 projects between 20171201 and 20171211 I would like to have a formula that will sum them up.

I am currently using this formula:

=SUMIFS(Sheet1!F:F,Sheet1!A:A,">20171201*",Sheet1!A:A,"<20171211*")

However, I always have to put the dates manually. What I want to do instead is to use dates from column A in Sheet2 to indicate the range of dates. You can download the Excel file from here. I pout there new formula that doesn't work as well.

I have tried to use this formula but it doesn't show me correct value.

=SUMIFS(Sheet1!F:F,Sheet1!A:A,">=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))*",Sheet1!A:A,"<=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))*")

Regards,

Adrian

1
excelexcel-formula

1 Answers

2
votes

If I understand what you want, SUMPRODUCT may be a better fit.

Something like:

=SUMPRODUCT((LEFT(projCosts[Project Name],8)>=TEXT(A2,"yyyymmdd"))*(LEFT(projCosts[Project Name],8)<TEXT(A3,"yyyymmdd"))*projCosts[Cost])

Where the dates of interest are in A2 and A3 (in this example). Note that, depending on the precise date range you want, you may need to change the equality operators (<,>=) to include/exclude the equal sign.