I've been given a file with 2 years worth of dates as column headers and sales info below. There's also a column that contains Year data. I'm trying to sort by both Year and Day of the Week. I'm about 4 hours in, and can't seem to solve this one, so any help would be greatly appreciated! I know I could filter by year, sum the columns, and HLOOKUP each day of the week -- I just figured there's a way to do this with an array formula that would simplify things.
Answer
You can usually use SUMPRODUCT for this - I'm not quite clear on the criteria for you but generically you can use this setup
=SUMPRODUCT((A2:A10=Z1)*(B1:J1=Z2),B2:J10)
where A2:A10 are the row labels, B1:J1 column headers and B2:J10 the data. Note the data range must be the same width as the column headers and same height as the row labels
Z1 and Z2 contain the criteria - you can use cell refs or put the criteria directly in the formula
If you can't adjust that then post back with more details
No comments:
Post a Comment