|
|
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)))
|
|
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
845 Views
(page loaded in 0.076 seconds)
Similiar Articles: counting occurences specific month appears - microsoft.public ...count - but exclude counting cells with a formula - microsoft ..... the formula seems to count all ... Counting cells with particular month and year ... formula for ... COUNTIFS with a date for criteria... - microsoft.public.excel ...... 2/28/2009") However, I use this similar function in multiple cells, all counting ... Is there a particular ... The criteria you use with COUNTIF functions ... count - but exclude counting cells with a formula - microsoft ...Counting cells with particular month and year - microsoft.public ... Thanks to help previously received, I have been using this formula to count the ... in excel, what is the formula that will add a number each month ...I would also like for the same day each year for one cell to be automatically be ... Increase cell value by 1 month - microsoft.public.excel.misc ..... these cells and ... count records by date range - microsoft.public.access.reports ...Counting cells with particular month and year - microsoft.public ... How to count cells with ... count records by date range ... for criteria... - microsoft.public.excel ... Counting of cells with a specific color filled - microsoft.public ...I need to count no of cells in a specific column... ... be filled with '1' in each month.... I managed to get it to calculate ... Counting of cells with a specific ... Formula to find text and count it within an entire workbook ...counting occurences specific month appears - microsoft.public ... 5 worksheets in my workbook, each representing a ... cells with particular month and year ... formula for ... Count Records for this week, this month, this quarter and this yea ...Count cells with specific text between two dates? - microsoft ... Subtract 1 Year from Query Date Range ... Count Records for this week, this month, this quarter and this ... Counting Cells with Multiple Range Criteria (Excel 2003 ...That will only work for that particular ... can't limit it to just the year. "Max" wrote: > > .. I want to count ... How to count cells that meet multiple criteria. Count cell changes - microsoft.public.excel.miscCounting of cells with a specific color ... How to count cells that meet ... Count Records for this week, this month, this quarter and this yea ... Count cell changes ... Excel - Count Cells With A Specific Year - I have a table ...Count Cells With A Specific Year - I have a table with ... for should give me a count of cells where the date in this format DD/MM/YYYY refers to a certain month and year (e ... Count the number of times dates in certain month appears in range ...» Count the number of times dates in certain month appears in range in Microsoft Excel ... Formula in cell D1: {=SUM((MONTH(A2:A11)=B2)*(YEAR(A2:A11)=C2)*1)} Result ... Sumproduct to count #s in specific month/year? - MrExcel Message BoardHoping to find out if (and how) sumproduct is right way to count #s that represent monthly salaries (in cells C3:BJ3) for the 60 months from Jan-04 to Dec-08 (in ... Excel - Count Specific Dates In Cell Range For Current Month - Hi ...Count Specific Dates In Cell Range For Current Month - Hi all I have ... I do not want to count any dates in the selected cells unless they are in the current month and year. Counting Cells with Excel 2007's COUNT Functions - For Dummies... COUNTIF — in the Statistical category that enable you to count the number of cells ... build to return such basic statistics as the total number of cells in a particular ... 7/18/2012 8:17:45 AM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
66 followers. 