Complicated Pivot Table Issue With SUMIF like logic.

read yellow banner at the top of this site
I am not sure the 2017 version of Excel (Office) exist, maybe on Mac ?

I updated it to office 365 enterprise. sandy666

maybe something like this?

Unit	Date	Sum of Funding	Sum of $Sales
MGMT	2022-01	70	404
	2022-03	15	302
	2022-05	25	203
	2022-07	35	402
	2022-09	45	9532
MGMT Total		190	10843
POWER	2022-03	75	893
	2022-04	20	439
	2022-11	55	2021
	2022-12	60	607
POWER Total		210	3960
SALES	2022-02	80	773
	2022-04	80	234
	2022-06	30	604
	2022-08	40	405
	2022-10	50	349
SALES Total		280	2365
Grand Total		680	17168

Originally Posted by sbellmore

I updated it to office 365 enterprise.

Thanks for that

I understand that method. I am trying to get it all on one line for the Unit instead of 5 separate ones. This pivot table is using a data set with 900,000 rows and many many "Unit" categories with a wide range of dates. Trying to keep it to the Unit and then one line of funding and sales

or like this

with date filter

Date	2022-03
Unit	Sum of $Sales	Sum of Funding
MGMT	302	15
POWER	893	75
Grand Total	1195	90

sandy666 the rest of the data is relevant to the month. The date slicer is relevant to this issue as you will only want to see one months worth of information. However the funding column is cumulative .. So when I use the date slicer I am getting the information for the summation of values with that same date. This is causing an issue because the funding is a summation of every date before the selected date with the same unit.

with the date filter on your picture table, the logic is wrong, because if the date was 2022-04 MGMT funding would return as 0 when MGMT funding is 15

if the date was 2022-04.. for MGMT it would also be the funding values for 2022-04, +03, +02, and +01 summed together (I know it only has data for 03 but you get the point).

I am a bit confused
could you manually show expected result with real dates not as text?

sandy666 I have uploaded a book with an elongated table with an example of what it should look like on the (2) sheet.

Originally Posted by sandy666

I am a bit confused
could you manually show expected result with real dates not as text?

I uploaded a secondary file book 1.1

why you didn't sum 2022-01 for MGMT, it is before 2022-11 and sum is 560 not 555

was a mistake sorry but yes was meant to be summed. Its acting almost as a running total based on criteria

so you can try Power Query

Filter
2022-11
	Unit	Fund	Sales
	MGMT	560	12496
	POWER	364	4672
	SALES	495	3142
	Total	1419	20310

What did you do to get that? I have been messing around in power query trying to solve this

see attached

the list for Filter you can do by formula or just hide column A

Sandy, damn that is close. The funding information is close but for 2022-11 the sales are 0