need formula

[ashfaqbsayed]

In sheet daily points from f5 to n5, i'm using vlookup to get data from pivot table and points next to pivot table, but i have to use vlookup formula from f5 till n5, is there any other method to get same data from all 9 pivot in f5, if u see i have sum from f5 to n5 in cell d5.

 

[Somendra Misra]

@ashfaqbsayed

What isthe problem with your current formula. You can just use an iferror to avoid #N/A error replaced by "" or a -.

Regards,

 

[ashfaqbsayed]

i want to increase space and reduce those line from f5 to n5, is there any way i should get direct answer like d5.

 

[AIM]

@ashfaqbsayed

You can use this in D5 :

=SUM(SUMPRODUCT(--($Q$5:$Q$24=$A5),($X$5:$X$24)),SUMPRODUCT(--($Y$5:$Y$54=$A5),($AF$5:$AF$54)),SUMPRODUCT(--($AG$5:$AG$54=$A5),($AN$5:$AN$54)),SUMPRODUCT(--($AO$5:$AO$54=$A5),($AV$5:$AV$54)),SUMPRODUCT(--($AW$5:$AW$54=$A5),($BD$5:$BD$54)),SUMPRODUCT(--($BE$5:$BE$54=$A5),($BL$5:$BL$54)),SUMPRODUCT(--($BM$5:$BM$54=$A5),($BT$5:$BT$54)),SUMPRODUCT(--($BU$5:$BU$54=$A5),($CB$5:$CB$54)),SUMPRODUCT(--($CC$5:$CC$54=$A5),($CJ$5:$CJ$54)))

Regards!!

 

[ashfaqbsayed]

Thank so much you made smile, you guys are awesome, appreciate,
once again thank you,
i love chandoo.org

 

[Somendra Misra]

@ashfaqbsayed

I had came up with a smaller formula, just try it on your dataset.
Enter in E5 and copy down,

=SUMPRODUCT(SUMIF($Q$5:$CR$24,$A5,$X$5:$X$24))

Regards,

 

[AIM]

@Somendra Misra, Nice formula

We can remove the SUMPRODUCT though

=SUMIF($Q$5:$CR$24,$A5,$X$5:$X$24)

Regards!!!