• 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.

how to count country with a figure?

F

fred

Member
I have a thousands of rows and only 2 columns of data. Column A: Country names. Column B: dollar figures. Some cells in column B is blank.


I want to make a summary table showing that Germany has 30 rows of data but only 20 cells with $.


The answer should be


Germany in cell A2000 and the number "20" in cell B2000. how do it count in such a way that it omit blank cells? By the way, "Germany" appears separately in rows 1 to 1600. they are not next to one another.
 
To Count cells with Germany: =COUNTIF(A2:A2000,"Germany")

To Count cells with a value and Germany: =SUMPRODUCT((A2:A2000="Germany")*(B2:B2000>0))

To Count cells without a value and Germany: =SUMPRODUCT((A2:A2000="Germany")*(B2:B2000=""))
 
WOW. I knew it should be sumproduct. But I didn't know you can use sumproduct this way. what i have learnt, from the old day, that sumproduct was just two series of numbers.


Thank you, Master Hui.


/bow
 
You must log in or register to reply here.
Back
Top