Problem with Countif and Sumif

[Constant]

Please have a look to the excel file in annex. I explain the problem inside the file and have already solve the problem on my way but I think not in an optimal way.
Thanks in advance.

 

[Debraj]

Hi..

Welcome to the forum..

In case of Count..
You can use Countifs..

=COUNTIFS($B$9:$D$20,$F9)

and in case of Sum..
Try this..
=SUM(MMULT(TRANSPOSE(IFERROR(MATCH($B$9:$D$20,F9,0),0)),$A$9:$A$20))

Confirm the formula by pressing Ctrl+Shift+Enter..
Not Just Enter..

 

[Nebu]

Hi:

A Much simpler array formula
=SUM(IF(($B$9:$D$20)=$F9,$A$9:$A$20))
=COUNT(IF(($B$9:$D$20)=$F9,$A$9:$A$20))
Thanks

 

[Debraj]

 

[Somendra Misra]

Hi All,

Here non-array version:

For count:
In G9: =SUMPRODUCT(COUNTIF(F9,$B$9:$D$20)) copy down

For Sum:
In H9: =SUMPRODUCT(COUNTIF(F9,$B$9:$D$20)*$A$9:$A$20) copy down

Regards,