Required Sumproduct formula with wild cards or ..........

[Basavaraj K H]

Dear Team

I need sumproduct formula for 12 months total sum of idea soga and idea Industry, please find the enclosed attachment for your reference


Thanks in Advance

Regards
Basavaraj K H

 

[NARAYANK991]

Hi ,

I am not sure I have understood you ; see the attached file and comment.

Narayan

 

[Deepak]

Put this in DJ3 & drag right/below
=SUMPRODUCT(ISNUMBER(SEARCH(DJ$2,$F$2:$AC$2))*$F3:$AC3)

 

[Basavaraj K H]

Thanks for your response

i want the result of column DL & DM in

 

[Deepak]

Thanks for your response

i want the result of column DL & DM in

Both said Col are blank.
So, Pls explain !!

 

[Basavaraj K H]

thanks for your Response,

i want the result of DL & DM column which i have mentioned in the attachment

 

[Deepak]

thanks for your Response,

i want the result of DL & DM column which i have mentioned in the attachment

You need to just change the range.

DJ2=SUMIF($D$2:$AC$2,"*" & DJ$2 & "*",$D3:$AC3)
or
DJ2=SUMPRODUCT(ISNUMBER(SEARCH(DJ$2,$D$2:$AC$2))*$D3:$AC3)

 

[Aby]

You need to just change the range.

DJ2=SUMIF($D$2:$AC$2,"*" & DJ$2 & "*",$D3:$AC3)
or
DJ2=SUMPRODUCT(ISNUMBER(SEARCH(DJ$2,$D$2:$AC$2))*$D3:$AC3)

Awesome formula Deepak- i ws trying with SUMPRODUCT duh

 

[Deepak]

Awesome formula Deepak- i ws trying with SUMPRODUCT duh

SUMIF formula was posted by @ NARAYANK991.

Thanks on behalf of him too..

 

[Hui]

You can use Sumproduct like:
=SUMPRODUCT((RIGHT($D$2:$AC$2,Len($DJ$2))=DJ$2)*($D3:$AC3))

if you want to sum all values by using a * or blank in DJ2 change the function to:
=SUMPRODUCT(IF(OR($DJ$2="",$DJ$2="*"),1,(RIGHT($D$2:$AC$2,LEN($DJ$2)) =$DJ$2))*($D3:$AC3))