Count of a number occurence by month

[Gandalf]

Hi

Could anyone help me. I am trying to write a formula to count how many times a particular number occurs any particular month. I have tried using both the countifs and aggregate formulas and either Excel stops me entering the formula or gives an error message. I've attached a part of the spreadsheet (with a comment) if that would help.

Many thanks

Gandalf

 

[Peter Bartholomew]

If you can magic up a copy of Excel 365, the formula below should work in cell K2.

 

[bosco_yip]

Try this old school formula way,

In K2, formula copied across and down :

=SUMPRODUCT((MONTH($A$2:$A$358)=MONTH(1&K$1))*COUNTIF(OFFSET($B$1:$F$1,ROW($B$2:$F$358)-ROW($B$1),0),$J2))

 

[p45cal]

In the attached, 2 offerings:
1. Formula only, see cell K2:
=SUMPRODUCT(--(IF(MONTH($A$2:$A$358)=MONTH(K$1),--($B$2:$F$358))=$J2))
copy down and across.
HOWEVER... note that in cells K1:V1, instead of the literal names of the months I've used actual dates but (a) only show the month in the top row and (b) in the formulae I only refer to the month of those dates in row 1, so the year there doesn't matter.
[edit post posting - bosco beat me to it!]

2. A Power Query solution which outputs to a pivot table at cell Y1.

Both seem to give the same answers, but check thoroughly.

 

[Gandalf]

A big thank you to all of you. As an Excel novice this might take me a little while to digest.