Sum Product not giving result

[Thomas Kuriakose]

Respected Sirs,

Kindly find attached our workbook where we have used sum product to calculate two values, but this is giving result as #N/A.

The four columns from AH1 to AK2 have error headers but this is coming from another data which has index and match and based on the entries the headers will be updated and hence this cannot be omitted.

Thank you very much,

with regards,
thomas

 

[xlstime]

Hi Thomas,

You can handle you error through IFERROR formula

use below formula with ctrl+shft+enter

=SUMPRODUCT(IFERROR($A$1:$AK$1="Motor",0)*IFERROR($A$2:$AK$2="AP",0)*IFERROR($A$3:$AK$4,0))

Respected Sirs,

Kindly find attached our workbook where we have used sum product to calculate two values, but this is giving result as #N/A.

The four columns from AH1 to AK2 have error headers but this is coming from another data which has index and match and based on the entries the headers will be updated and hence this cannot be omitted.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

The second one can also be done using IFERROR. See the attached file.

Narayan

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for this solution and for the guidance.

Thanks once again,

with regards,
thomas

 

[bosco_yip]

Hi,

Another option as per attachment

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this option.

with regards,
thomas

 

[bines53]

Hi,

It is also possible that,

=SUM(COUNTIFS(C17,A1:AK1,D17,A2:AK2)*A3:AK4)
=SUM((COUNTIF(C17,A1:AK1)+COUNTIF(E17,A1:AK1))*COUNTIF(D17,A2:AK2)*A3:AK7)
An array

David

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

Much appreciated,

with regards,
thomas