Search code examples
excelwindowssumifs

Total sum using specific text and date

I was wondering if anyone can help me with this as I have had a look everywhere online and can’t seem to get the correct answer.

I have a very large complicated excel sheet that contains products I have bought over several months.

What I am trying to achieve is to do a total sum using specific text within a time frame. I have managed to achieve the total sum using specific Text by using this formula

=SUMIF(A2:A8,"apple",B2:B8)

But now what I want to do is add an extra string if possible to look between dates so that it looks for the sum apple of ‘apple’ within January.

Example of sheet

   A         B        C
product   price     date
apple     £150.00   Jan-16
apple     £150.00   Feb-16
pear      £100.00   Jan-16
pear      £100.00   Feb-16
apple     £150.00   Jan-16
banana    £200.00   Feb-16
banana    £200.00   Feb-16


Apple Total   
Jan       Feb       March
**£450.00**   

Pear Total    
Jan       Feb       March
Solution

  • Your dates look as if they are dates showing just month and day, so Jan-16 would be stored as 1-Jan-16. In this case you could use a SUMIFS like this

    =SUMIFS($B$2:$B$8,$A$2:$A$8,$E1,$C$2:$C$8,DATEVALUE(E2&"-16"))

    where the fruit to be matched is in E1 and the month in E2.

    To match dates anywhere in the month, you could use EOMONTH

     =SUMIFS($B$2:$B$8,$A$2:$A$8,$E1,$C$2:$C$8,">="&DATEVALUE(E2&"-16"),$C$2:$C$8,"<="&EOMONTH(DATEVALUE(E2&"-16"),0))

    Have changed first date in the screen shot below to illustrate the two formulae

    enter image description here