Excel create a summary using a drop down, obtaining data from another sheet using sumif formula

0
votes

I have a spread sheet I've been working on and would like to create a summary sheet within the workbook to make it more user friendly (avoiding using VBA)

I want the user to be able to select a month (1-12) from a drop down in the sheet named "Summary" and the data to be populated from the "Actuals" sheet.

Currently I manually update the formula:

"=SUMIF(Acutals!A:A,Summary!A5,Acutals!B:B)"

and move the "Acutals!B:B" along a column when I want the next months data (January = B:B, February = C:C, March = D:D etc.).

Can I use the indirect function and sumif (to find the correct account description) to refer to the drop down in cell B1 on the Summary Sheet and link that to the correct month column on the Actuals sheet?

Summary worksheet Actuals worksheet

1
excelexcel-formula
Have you tried using a data validation? Check this link which will give you an idea of how to accomplish this. – Birel

1 Answers

0
votes
=SUMPRODUCT((Acutals!$B$5:$M$200)*(Acutals!$A$5:$A$200=$B5)*(Acutals!$B$3:$M$3=$B$1))

unlike SUMIFS, SUMPRODUCT can be used on multiple columns but cannot reference whole columns eg A:A.