XLOOKUP or VLOOKUP help.

Occasional Contributor

So I am trying to use the VLOOKUP or XLOOKUP function to find corresponding values in my data.

 

Say I have some financials listed quarterly:

2015

Q1 - $2000

Q2 - $3000

Q3 - $4000

Q4 - $1000

2016

Q1 - $4000

Q2 - $500

Q3 - $1000

Q4 - $5000

and so on. 

 

Is there a way I can find the sum of the values for all of one specific quarter?

All I seem to know is -

XLOOKUP(Q3,A2:A50,B2:B50) but this would just return one value rather than summing all the Q3 results.

 

If that makes sense.

 

Thanks.

 

5 Replies

@cameronmarshall 

I guess SUMIFS() could help

@cameronmarshall 

=SUMPRODUCT((A1:A19=B22)*B1:B19)

 

Is this what you are looking for?

From your reply I can't seem to think why ti wouldn't work however I'm getting a #VALUE error. Any ideas?

@cameronmarshall 

Did you enter e.g. "Q1" and "2000" in seperate cells? It doesn't work if "Q1 - 2000" is in one cell as shown in the attached file. Maybe you want to attach an example copy of your file with dummy data.

@cameronmarshall 

As variant

=SUM( FILTER( B2:B50, A2:A50 = "Q3" ) )