• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with Countif and Sumif

Debraj

Excel Ninja
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..
 

Attachments

Nebu

Excel Ninja
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
 

Somendra Misra

Excel Ninja
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,
 
Top