Aug 26 2020 11:48 AM
Hi There,
I've completed a data query and have a long list in Sheet A with three columns:
Column A are dates
Column B are the same 5 items repeated every five rows, let's say
Revenues
CoGS
Payroll Expense
Admin Expense
Net Income
Column C are the values
Sheet B has the dates across the top row and the items in column B in the first column
I want to search the data in Sheet A and fill in the values in Sheet B to match the date in the top row and the label in the first column.
Can anyone suggest a formula for that?
Thank you
Aug 26 2020 11:59 AM
In B2 on Sheet B:
=SUMIFS('Sheet A'!$C$2:$C$21,'Sheet A'!$A$2:$A$21,B$1,'Sheet A'!$B$2:$B$21,$A2)
Change Sheet A to the actual name of the first sheet, and adjust the ranges as needed.
Fill to the right, then down, or vice versa.
Aug 26 2020 12:14 PM
@Hans Vogelaar so simple! Thank you. Someone kept telling me to use VLOOKUP and I was struggling.
Aug 26 2020 01:36 PM
@Hans Vogelaar sorry another problem. The data I am referring to in Sheet A is in a data table. When I point to the table in Sheet A, I get the correct values, but the columns are relative references so I cannot copy them across in Sheet B. If I try to create a reference using 'Sheet A'!$b$2:$b$109, the sumif does not find the value. This is my first time using data tables outside of the classroom so I am wondering if there is some trick to referring to table references absolutely. Or do I need to take the data out of table format?
Aug 26 2020 02:18 PM
The formula does indeed not work correctly if you use structured table references, but as far as I can tell it should work OK if you use range references even if the data on Sheet A are in a table.
Could you attach a sample workbook without sensitive/proprietary data?
Aug 26 2020 02:38 PM
@Hans Vogelaar here is a sample - Sheet 2, Column Z has an example of what I want to see and be able to copy across to other dates. Sheet A currently has just one year and location but I think that is enough to solve the problem with.
Aug 26 2020 02:54 PM
This should work in Z3:
=SUMIFS(_2019__7[[Column2]:[Column2]],_2019__7[[Column1]:[Column1]],$A3,_2019__7[[Column3]:[Column3]],Z$1)
This can be filled to the right. See the attached version.
The idea is from Absolute Structured References in Excel Table Formulas
Aug 26 2020 03:00 PM
@Hans Vogelaar thanks again for your help!! It works great!
Aug 26 2020 10:23 PM
@karenynp Why not use a pivot table. Your data is perfectly structured for it. No need for formulae and you can easily adopt the tables when you enter data for more than one year and/or location.
See attached.