Sumifs Index Match formula

[Malhotra Rahul]

Hi, I do have a small situation, where i want output based on Raw Data tab in Report tab in Cell C22 and D22. For Cell C22 and D22 the first criteria would be Start Date in Cell B18 in Report tab and End Date in Cell C18 in Report tab and the second criteria would be the Account Manager in Report tab table.

I have provided the expected results in Report tab. Any help would highly be appreciated. Thank you in Advance.

Please find here below the link to get sample file.

https://drive.google.com/file/d/16f4r0y9aCRBN1nyFFX1RstxzGHfcNCtH/view?usp=sharing

 

[Aladin Akyurek]

Raw Data 2018

Account Manager	Date of Order	Service Hours	Perpetual License Rev	ASM	Pro-Rated Invoiced ASM	Monthly License Fee	Monthly Hosting Fee	Services Rev.	Total
ABC	1/2/2018	4.00	$ 0.00	$ 20.00	$ 0.00	$ 0.00	$ 0.00	$ 740.00	$ 760.00
ABC	1/4/2018	3.00	$ 0.00	$ 45.00	$ 0.00	$ 675.00	$ 0.00	$ 250.00	$ 970.00
DEF	3/4/2018	16.00	$ 0.00	$ 0.00	$ 0.00	$ 0.00	$ 0.00	$ 2,960.00	$ 2,960.00
GHI	4/5/2018	2.50	$ 0.00	$ 0.00	$ 0.00	$ 0.00	$ 0.00	$ 462.50	$ 462.50

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Report

Start Date	End Date
1/1/2018	1/31/2018
Account Manager Name	Service Hours	Service Revenue
ABC	7	990
DEF	0	0
GHI	0	0
KLM	0	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

C22 of Report...

=SUMIFS('Raw Data 2018'!$C$3:$C$6,'Raw Data 2018'!$A$3:$A$6,B22,'Raw Data 2018'!$B$3:$B$6,">="&$B$18,'Raw Data 2018'!$B$3:$B$6,"<="&$C$18)

D22 of Report...

=SUMIFS('Raw Data 2018'!$I$3:$I$6,'Raw Data 2018'!$A$3:$A$6,B22,'Raw Data 2018'!$B$3:$B$6,">="&$B$18,'Raw Data 2018'!$B$3:$B$6,"<="&$C$18)

 

[Malhotra Rahul]

Hi Aladin akyurek, thank you so much for the solution, is there any way to get the solution matching with the columns heads. Because I'm looking for the match based on columns header.

 

[Aladin Akyurek]

Hi Aladin akyurek, thank you so much for the solution, is there any way to get the solution matching with the columns heads. Because I'm looking for the match based on columns header.

Then first you need to change

Services Rev.​

to

Service Revenue​

.

That done:

In C22 enter, copy across, and down:

=SUMIFS(INDEX('Raw Data 2018'!$A$3:$J$6,0,MATCH(C$21,'Raw Data 2018'!$A$2:$J$2,0)),'Raw Data 2018'!$A$3:$A$6,$B22,'Raw Data 2018'!$B$3:$B$6,">="&$B$18,'Raw Data 2018'!$B$3:$B$6,"<="&$C$18)

 

[Malhotra Rahul]

Thank you Aladin Akyrek, Its worked perfectly. Once again thank you so much.

 

[Aladin Akyurek]

Thank you Aladin Akyrek, Its worked perfectly. Once again thank you so much.

You are welcome. Thanks for the update.