Counting cells with particular month and year
 

Thanks to help previously received, I have been using this formula to count
the number of referrals received in a particular month. Is it possible to
include the year as well?

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10)))

Counting cells with particular month and year
 

Now the Year also included in the below formula the year reference cell is
referred in B1 cell of STATS sheet.

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10)))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Saylindara" wrote:

Thanks to help previously received, I have been using this formula to count
the number of referrals received in a particular month. Is it possible to
include the year as well?

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10)))

Counting cells with particular month and year
 

Try

With month in A1 and year in A2
=SUMPRODUCT(--(MONTH(A2:A10)=sTATS!A1)*(YEAR(A2:A10)=sTATS!A2),--(ISNUMBER(B2:B10)))

OR with a valid date in Stats!A1

=SUMPRODUCT((TEXT(A2:A10,"mmyyyy")=TEXT(sTATS!A1," mmyyyy"))*(ISNUMBER(B2:B10)))

--
Jacob


"Saylindara" wrote:

Thanks to help previously received, I have been using this formula to count
the number of referrals received in a particular month. Is it possible to
include the year as well?

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10)))

Counting cells with particular month and year
 

You don't need the double unary minus with the multiplication.
There is nothing magical about the use of double unary minus; to convert a
boolean to a number you need to do an arithmetic operation on it. Two
negations is an easy option, but multiplying by 1 or adding zero are other
equally acceptable options. In your case you are multiplying two booleans,
so they are automatically converted to numbers, and your double unary minus
on each of the multiplicands add no additional functionality.

You can change
=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10)))
to
=SUMPRODUCT((MONTH(A2:A10)=Stats!A1)*(YEAR(A2:A10) =Stats!B1),--(ISNUMBER(B2:B10)))
or
=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10)))
or
=SUMPRODUCT((MONTH(A2:A10)=Stats!A1)*(YEAR(A2:A10) =Stats!B1)*(ISNUMBER(B2:B10)))
--
David Biddulph

"Ms-Exl-Learner" wrote in message
...
Now the Year also included in the below formula the year reference cell is
referred in B1 cell of STATS sheet.

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10)))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Saylindara" wrote:

Thanks to help previously received, I have been using this formula to
count
the number of referrals received in a particular month. Is it possible to
include the year as well?

=SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10)))