Counting cells with particular month and year

  • Follow


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))) 


0
Reply Utf 12/9/2009 7:00:05 AM

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))) 
> 
> 
0
Reply Utf 12/9/2009 7:21:02 AM

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))) 
> 
> 
0
Reply Utf 12/9/2009 8:04:01 AM

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" <Ms.Exl.Learner@gmail.com> wrote in message 
news:A25064F6-0EED-4E0F-86BE-95CEE07A297B@microsoft.com...
> 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)))
>>
>> 


1
Reply David 12/9/2009 8:55:40 AM

3 Replies
1404 Views

(page loaded in 4.785 seconds)


Reply: