Formula help

[hkelly393]

I'm trying to work a formula that searches a column header for a particular quarter, in this example, Q1-2021 and then finds the Total Revenue line and pulls the value from the same column with the matching header;

monosnap.com

Monosnap screenshot tool for Mac and PC with own cloud storage. Take screenshots, record videos and upload files directly to the cloud. Just in one click.

monosnap.com

I have this but it won't search the quarter first.
=SUMIF('ELM JAN'!$A:$A, " Total Revenue", 'ELM JAN'!$B:$B)

 

[AhoyNC]

Try INDEX/MATCH
See example below.

Book1
	A	B	C
1		Q1-2021	Q4-2020
2	Line1	5	2
3	Line2	10	4
4	Line3	15	6
5	Line4	20	6
6	Line5	25	8
7	Line6	30	14
8	Total Revenue	105	20
9
10	Find	Q1-2021
11		105
Sheet1

Cell Formulas
Range	Formula
B11	B11	=INDEX($B$8:$C$8,MATCH($B$10,$B$1:$C$1,0))

 

[jtakw]

Hi,

If you also need the "Month Ending" as criteria, you can use this:

If the Revenue Line may change month to month, use B15 formula,
otherwise if the Revenue line is Static, you just need to change from SUMIF to SUMIFS:

Book3.xlsx
	A	B	C	D	E
1
2		Q1-2021	Q4-2020	Q4-2020
3		Month Ending	Month Ending	Month Ending
4		1/31/2021	12/31/2020	11/30/2020
5	Line1	5	2	7
6	Line2	10	4	8
7	Line3	15	6	9
8	Line4	20	6	10
9	Line5	25	8	11
10	Line6	30	14	12
11	Total Revenue	105	20	13
12
13	Quarter-Year	Q1-2021	Q4-2020
14	Month Ending	1/31/2021	11/30/2020
15	Total Revenue	105	13
16		105	13
Sheet837

Cell Formulas
Range	Formula
B15:C15	B15	=SUMPRODUCT(($B2:$Z2=B13)*($B4:$Z4=B14)*($A5:$A11=$A15)*$B5:$Z11)
B16:C16	B16	=SUMIFS($B11:$Z11,$B2:$Z2,B13,$B4:$Z4,B14)