Sumifs

[Vegas01]

Hi everyone

I have 12 months, Trying to work out YTD based on a number of other critera below is what I currently have which is returning #value

=SUMIFS('BUDGET FY23 GP'!$Q$5:$AB$67,'BUDGET FY23 GP'!$AE:$AE,$B6,'BUDGET FY23 GP'!$Q$1:$AB$1,"<="&'ACTUAL P&L FY23'!$AF$1)

Q5:AB67 are the amounts that need to be added up
AE:AE - is the first reference that is being looked up
Q1:AB1 - are the months - number 1 -12
AF1 = 3

I am trying to get the formula to add up the first 3 colums

Row 2 should = 37,350
Row 4 should = 33,000
Row 6 should = 75,000

Thanks in advance

 

[StephenCrump]

You can get YTD row totals like this:

	A	B	C	D	E	F	G	H	I
1					Month
2		YTD to month	3		1	2	3	4	5 ....
3
4			10		2	3	5	7
5			41		11	13	17	19
6			83		23	29	31	37
7
Tables

Cell Formulas
Range	Formula
C4:C6	C4	=SUMPRODUCT(E4:I4,--(E$2:I$2<=C$2))

It's not clear what the other condition in your SUMIFS is doing?

 

[Vegas01]

Stephen that works on for a individual line but as I have a table of data, the formula needs to pick up the other criteria which was what the other condition is doing the Sumif

Thanks

 

[StephenCrump]

It's easy to add more conditions .... but we'll need more information.

Bear in mind you've posted a limited screenshot as a picture, hence we have no idea what's in 'BUDGET FY23 GP'!$AE:$AE or $B6, or how these cells relate to what you've posted.

 

[Vegas01]

Stephen

Here you go I have added some more data.

 

[StephenCrump]

Here's one way:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10	11	12
2	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Total	Criteria
3	2	5	7	8	8									Co
4	1	110	59	2	2									GS
5	2	7	2	8	9									Co
6	7	8	8	2	2									GS
7
8	Period	3
9
10	Co	25
11	GS	193
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=SUM((A$1:L$1<=B$8)*(N$3:N$6=A10)*A$3:L$6)
Press CTRL+SHIFT+ENTER to enter array formulas.