Formula to return values from a spreadsheet with specific parameters

Hi,

Your data does not lend itself to a Pivot Table. I've reformatted it in the attached sheet and added a PT for you. I've also added a normal table with SUMIFS() formulae which contains the same results. A PT is IMO much to be preferred since it's far more flexible and can be diced/sliced in any way you could conceivably want.

Hi,

A report not being in the right format for a PT is a common occurence. The answer usually is to have a pre-processing macro that reformats the data you are presented with into a format a PT can use. In your case it's fairly straightforward. The steps you need to take are

1. On a new sheet create the 4 column headings, Date, Organisation, Day, Amount
On a new sheet copy the values from A6:Bnn to the next available cell in column B on the new sheet
2. Copy the C2:Cnn values from your data to C2 on the new sheet
3. Fill the range A2:Ann with the dates from the data column C header

If there is data in the next column D repeat steps 2 & 3 above.

4. Finally filter the new data for rows that are either blank in column A or contain the text "Totals" and delete these rows.

You then have the source data for a PT. Try using the macro recorder to create a macro to do it. That will give you a good starting point. The code it produces will need editing to replace hard coded cell addresses. But when you're at that stage post back and no doubt we'll be able to develop it further.

Add column headers like this to your existing data so you have an arrangement like this:

	A	B	C	D
4	Organisation	Weekday	Jan-16	Feb-16
5	Totals		42	11
6	Organisation 1	Tuesdays	17	2
7	Organisation 1	Wednesdays	15	5
8	Organisation 1	Thursdays	10	4
9
10			Jan-16	Feb-16
11	Totals		157	49
12	Organisation 2	Tuesdays	23	11
13	Organisation 2	Wednesdays	89	15
14	Organisation 2	Thursdays	45	23
15
16			Jan-16	Feb-16
17	Totals		39	10
18	Organisation 3	Tuesdays	15	5
19	Organisation 3	Wednesdays	14	4
20	Organisation 3	Thursdays	10	1

Row 4 shows titles for each column.

Select all the data including row 4 with the headers. Click on the Insert tab, Pivot Table
Drag Organization to the row labels box, and the dates to the Values box. Change the Value Field Settings for each of the dates from Count to Sum.

With the Row Labels filter, deselect Totals and Blank. Change the column headers of the pivot table to suit.
Result:

	A	B	C
3	Organization	Jan-16	Feb-16
4	Organisation 1	42	11
5	Organisation 2	157	49
6	Organisation 3	39	10
7	Grand Total	238	70