Sumif with Vlookup and Match and Data Validation

[ysherriff]

I have a formula issue I need to resolve. I need to create a formula where I have a data validation dropdown and I need to create a formula that will search a database list based on the dropdown selection.

I am just having diffuculty combining Sumif with vlookup and multiple match functions. I am uploading a file as an example. In the attached file, for instance, I want to sum all the value for Agility for the day of the week and selected dropdown.

Thanks for your help.
Th

 

[Somendra Misra]

Hi ysherriff,

Try below formula in C23 and copy right and down:

=SUMPRODUCT(('Data Monthly by Days'!$E$6:$BN$320)*('Data Monthly by Days'!$B$6:$B$320='Trend by Days'!C$22)*('Trend by Days'!$B$5=HEADER_BY_DAYS)*('Trend by Days'!$B23=DAYS_OF_MONTH))

Regards,

 

[ysherriff]

Thank you very much Somendra. Worked perfectly. I am not strong in sumproduct formula and utilize sumifs alot so I appreciate it. But how would you use this with sumif and vlookup or index match functions.

Just curious.

Thanks a million again.

 

[NARAYANK991]

Hi ,

Try this :

=SUMIFS(INDEX('Data Monthly by Days'!$E$6:$BN$320,0,MATCH($B$5,HEADER_BY_DAYS,0) + MATCH($B23,DAYS_OF_MONTH,0)-1) , 'Data Monthly by Days'!$B$6:$B$320 , 'Trend by Days'!C$22)

Narayan

 

[ysherriff]

Hi ysherriff,

Try below formula in C23 and copy right and down:

=SUMPRODUCT(('Data Monthly by Days'!$E$6:$BN$320)*('Data Monthly by Days'!$B$6:$B$320='Trend by Days'!C$22)*('Trend by Days'!$B$5=HEADER_BY_DAYS)*('Trend by Days'!$B23=DAYS_OF_MONTH))

Regards,

Somendra,

How can I use this to total percentages. I have attached example. I can not do a lookup using sumproduct on values that are percentages. Can you help with formula.

Thanks

 

[Somendra Misra]

Hi Ysherriff,

See the attached file. The dates were different on both the sheets.

I had also inserted SUBTOTAL function on row E321 and copied to the right on sheet Trend by data chart. This will automatically update average if you apply filter on your data table.

Regards,

 

[ysherriff]

Thank you. But how do I get the average instead of sum. Sumproduct is adding all the percentages, but i want to get the average.

Thank you very much.

 

[ysherriff]

I believe i will have to use mulitple averageif statements. There is no other way around it because I am making the report 2003 compatible and cannot use averageifs.

 

[ysherriff]

nevermind again. I just used countif....... it works

 

[Somendra Misra]

Try below formula in C24 on Trend By Days Sheet and copy right and down:

=(SUMPRODUCT('Trend by Days Chart Calx Data'!$E$6:$N$320*('Trend by Days Chart Calx Data'!$B$6:$B$320='Trend by Days'!C$23)*('Trend by Days'!$B$5='Trend by Days Chart Calx Data'!$E$4:$N$4)*('Trend by Days'!$B24='Trend by Days Chart Calx Data'!$E$5:$N$5)))/COUNTIF('Trend by Days Chart Calx Data'!$B$6:$B$320,'Trend by Days'!C$23)

Regards,