SUMIF and Search

[Super Mario]

Hi,
I'm struggling to figure out why this formula wont work, thought this would be an easy one for me!

I have a quarterly budget against business unit projects, several projects per business unit that I want to summarise. Q1 to Q4 budgets for each project listed in columns L to O. The criteria range is column J and the criteria is column A.

In the summary table, I want to sum the projects by BU in column C. Now I want to change the sum of budget by changing the quarter I'm viewing by a data validation list in cell B1. List is FY24 Q1, FY24 Q2, FY24 Q3, FY24 Q4.

The problem I have is that when I change the quarter in cell B1, the formula errors out and I can't seem to fix this. (so it works only with FY24 Q1 is selected, but not for any other quarters). If I'm missing a simple correction, or if there is a better set of formulas to use then I would really appreciate some help with this.

=IF(SEARCH("Q1",B1,1),SUMIF(J2:J13,A5,L2:L13),IF(SEARCH("Q2",B1,1),SUMIF(J2:J13,A5,M2:M13),IF(SEARCH("Q3",B1,1),SUMIF(J2:J13,A5,N2:N13),IF(SEARCH("Q4",B1,1),SUMIF(J2:J13,A5,O2:O13),"error"))))

Many thanks.

 

[RoryA]

What exactly are the headers in L1:O1? The simplest way will be some sort of INDEX/MATCH combination like:

=SUMIF(J2:J13,A5,INDEX(L2:O13,0,MATCH(B1,L1:O1,0)))

 

[Super Mario]

Yes headers are the same. Thanks, this works.