Selective count with two variables

[equinox1974]

Hi All,

I am stuck trying to create a formula to selectively count values based on the data in two columns. As attached is a spreadsheet of the number of times it rained dogs or cats over a number of months. I want to know how many times it rained dogs in a particular month, and how many times it rained cats in a particular month!

Any help would be greatly appreciated!

Thanks!

[sandy666]

try: =SUMPRODUCT((Table5[Raining]=$E$1)*(MONTH(Table5[Date])=$F$1))

[TMS]

Use a Pivot Table and group by month

[equinox1974]

Thanks All,

Is there a way to group the Pivot Table data by singular month, rather than having multiple instances of for example January, or 5 instances of February for cats etc?

[TMS]

Right click on one of the dates listed. From the dialogue, choose Group and then Month

[equinox1974]

Thanks All, fantastic support!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.