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

[TABLE="class: grid, width: 753"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Account Manager[/TD]
[TD]Date of Order[/TD]
[TD]Service Hours[/TD]
[TD]Perpetual License Rev[/TD]
[TD]ASM[/TD]
[TD]Pro-Rated Invoiced ASM[/TD]
[TD]Monthly License Fee[/TD]
[TD]Monthly Hosting Fee[/TD]
[TD]Services Rev.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1/2/2018[/TD]
[TD="align: right"]4.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 20.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 740.00[/TD]
[TD]$ 760.00[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]1/4/2018[/TD]
[TD="align: right"]3.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 45.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 675.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 250.00[/TD]
[TD]$ 970.00[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]3/4/2018[/TD]
[TD="align: right"]16.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 2,960.00[/TD]
[TD]$ 2,960.00[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]4/5/2018[/TD]
[TD="align: right"]2.50[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 0.00[/TD]
[TD]$ 462.50[/TD]
[TD]$ 462.50[/TD]
[/TR]
</tbody>[/TABLE]


Report

[TABLE="class: grid, width: 423"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/31/2018[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Account Manager Name[/TD]
[TD]Service Hours[/TD]
[TD]Service Revenue[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]990[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]KLM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


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.