Nested COUNTIF?
 

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the month
of Jan of example.

Please help.
Thank you.
Best Regards...

Nested COUNTIF?
 

=SUMPRODUCT((B2:B5=1)*(MONTH(C2:C5)=1)*(YEAR(C2:C5 )=2008))


"Astro" wrote:

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the month
of Jan of example.

Please help.
Thank you.
Best Regards...

Nested COUNTIF?
 

Teethless mama wrote...
=SUMPRODUCT((B2:B5=1)*(MONTH(C2:C5)=1)*(YEAR(C2:C 5)=2008))
....

Or

=SUMPRODUCT(B2:B5*(TEXT(C2:C5,"YYYY-MM")="2008-01"))

or

=SUMIF(C2:C8,"="&--"2008-01-01",B2:B8)-
SUMIF(C2:C8,"="&--"2008-02-01",B2:B8)

Nested COUNTIF?
 

Harlan,

As someone who (rightly) picks up numerous posters on formula efficiency, I
am surprised to see you suggesting that text method in response to TM's
offering, it is some 2 times as slow.

You did redeem yourself with SUMIF though (<g), that is some 30% faster in
my tests.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Harlan Grove" wrote in message
...
Teethless mama wrote...
=SUMPRODUCT((B2:B5=1)*(MONTH(C2:C5)=1)*(YEAR(C2: C5)=2008))
...

Or

=SUMPRODUCT(B2:B5*(TEXT(C2:C5,"YYYY-MM")="2008-01"))

or

=SUMIF(C2:C8,"="&--"2008-01-01",B2:B8)-
SUMIF(C2:C8,"="&--"2008-02-01",B2:B8)

Nested COUNTIF?
 

"Bob Phillips" wrote...
As someone who (rightly) picks up numerous posters on formula efficiency, I
am surprised to see you suggesting that text method in response to TM's
offering, it is some 2 times as slow.

You did redeem yourself with SUMIF though (<g), that is some 30% faster in
my tests.
....
"Harlan Grove" wrote in message
....
=SUMIF(C2:C8,"="&--"2008-01-01",B2:B8)-
SUMIF(C2:C8,"="&--"2008-02-01",B2:B8)

Yeah, but

=SUMPRODUCT(B2:B8,--(ABS(C2:C8-"2008-01-16")<=15))

should be faster.

Nested COUNTIF?
 

Not in my tests, marginal but consistently 2-5% slower.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
As someone who (rightly) picks up numerous posters on formula efficiency,
I
am surprised to see you suggesting that text method in response to TM's
offering, it is some 2 times as slow.

You did redeem yourself with SUMIF though (<g), that is some 30% faster
in
my tests.
...
"Harlan Grove" wrote in message
...
=SUMIF(C2:C8,"="&--"2008-01-01",B2:B8)-
SUMIF(C2:C8,"="&--"2008-02-01",B2:B8)

Yeah, but

=SUMPRODUCT(B2:B8,--(ABS(C2:C8-"2008-01-16")<=15))

should be faster.

Similar question
 

My issue is similar: One column has dates (for the whole year) and another
has 1 of 4 words entered. I need to count the number of times a word begining
w/ 'D' is entered per month.
Anyone?

Nested COUNTIF?
 

My issue is similar, but I have one column of dates and another of various
word entries. I need to count the number of times a word begining with 'D'
was entered per month.

Any help is appreciated. Thanks





"Astro" wrote:

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the month
of Jan of example.

Please help.
Thank you.
Best Regards...

Nested COUNTIF?
 

Try something like this:

=SUMPRODUCT(--(LEFT(A1:A10)="D"),--(MONTH(B1:B10)=n))

Where n = the month number: 1 = Jan thru 12 = Dec

--
Biff
Microsoft Excel MVP


"Haydie-lady" wrote in message
...
My issue is similar, but I have one column of dates and another of various
word entries. I need to count the number of times a word begining with 'D'
was entered per month.

Any help is appreciated. Thanks





"Astro" wrote:

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the
month
of Jan of example.

Please help.
Thank you.
Best Regards...

Nested COUNTIF?
 

hmmm..... It's not working. Does it matter that the data is in a different
worksheet than the function? OR that i need to include/search the whole
entire column of data b/c this log is being continually added to?


"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(LEFT(A1:A10)="D"),--(MONTH(B1:B10)=n))

Where n = the month number: 1 = Jan thru 12 = Dec

--
Biff
Microsoft Excel MVP


"Haydie-lady" wrote in message
...
My issue is similar, but I have one column of dates and another of various
word entries. I need to count the number of times a word begining with 'D'
was entered per month.

Any help is appreciated. Thanks





"Astro" wrote:

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the
month
of Jan of example.

Please help.
Thank you.
Best Regards...

Nested COUNTIF?
 

Does it matter that the data is in a
different worksheet than the function?

No

i need to include/search the whole
entire column of data

Unless you're using Excel 2007 you *can't* use entire columns as range
references. Use the smallest range possible:

=SUMPRODUCT(--(LEFT(A1:A10000)="D"),--(MONTH(B1:B10000)=n))

Another method is to use a dynamic range. See this:

http://contextures.com/xlNames01.html#Dynamic


--
Biff
Microsoft Excel MVP


"haydie-lady" wrote in message
...
hmmm..... It's not working. Does it matter that the data is in a different
worksheet than the function? OR that i need to include/search the whole
entire column of data b/c this log is being continually added to?


"T. Valko" wrote:

Try something like this:

=SUMPRODUCT(--(LEFT(A1:A10)="D"),--(MONTH(B1:B10)=n))

Where n = the month number: 1 = Jan thru 12 = Dec

--
Biff
Microsoft Excel MVP


"Haydie-lady" wrote in message
...
My issue is similar, but I have one column of dates and another of
various
word entries. I need to count the number of times a word begining with
'D'
was entered per month.

Any help is appreciated. Thanks





"Astro" wrote:

Currently, I have this worksheet with the following datas:

Items Accepted Date
Apple 1 30-Jan-08
Mango 0 15-Jan-08
Orange 1 20-Feb-08
Pear 1 10-Jan-08

What function must I use to count the number of accepted items in the
month
of Jan of example.

Please help.
Thank you.
Best Regards...

Similar question
 

If you have the dates in A1:A10 and the words in B1:B10, use the
following array formula:

=SUM((MONTH(A1:A10)=2)*(LEFT(B1:B10,1)="d"))

Change the '2' to the appropriate month number (between 1 and 12).

Adjust the ranges to meet your needs. Since this is an Array Formula,
you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 19 Mar 2009 08:25:02 -0700, Haydie-lady
wrote:

My issue is similar: One column has dates (for the whole year) and another
has 1 of 4 words entered. I need to count the number of times a word begining
w/ 'D' is entered per month.
Anyone?

Similar question
 

Chip,
I really like this function, but my work sheet includes data from previous
years as well. I need to be able to count "d" words during each month of each
year. Any suggestions?

"Chip Pearson" wrote:

If you have the dates in A1:A10 and the words in B1:B10, use the
following array formula:

=SUM((MONTH(A1:A10)=2)*(LEFT(B1:B10,1)="d"))

Change the '2' to the appropriate month number (between 1 and 12).

Adjust the ranges to meet your needs. Since this is an Array Formula,
you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 19 Mar 2009 08:25:02 -0700, Haydie-lady
wrote:

My issue is similar: One column has dates (for the whole year) and another
has 1 of 4 words entered. I need to count the number of times a word begining
w/ 'D' is entered per month.
Anyone?

Similar question
 

Just add a test for the year number:

Normally entered:

=SUMPRODUCT(--(LEFT(A1:A10)="D"),--(MONTH(B1:B10)=n),--(YEAR(B1:B10)=2009))

Array entered:

=SUM((LEFT(A1:A10)="D")*(MONTH(B1:B10)=n)*(YEAR(B1 :B10)=2009))


--
Biff
Microsoft Excel MVP


"Haydie-lady" wrote in message
...
Chip,
I really like this function, but my work sheet includes data from previous
years as well. I need to be able to count "d" words during each month of
each
year. Any suggestions?

"Chip Pearson" wrote:

If you have the dates in A1:A10 and the words in B1:B10, use the
following array formula:

=SUM((MONTH(A1:A10)=2)*(LEFT(B1:B10,1)="d"))

Change the '2' to the appropriate month number (between 1 and 12).

Adjust the ranges to meet your needs. Since this is an Array Formula,
you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 19 Mar 2009 08:25:02 -0700, Haydie-lady
wrote:

My issue is similar: One column has dates (for the whole year) and
another
has 1 of 4 words entered. I need to count the number of times a word
begining
w/ 'D' is entered per month.
Anyone?

Text to columns
 

500+ sentences, I need to seperate (text to columns) after the first "-"
only. There are subsequent "-" but I only want it seperated at the first.
And when I seperate it in the second column I do not want a space preceeding
the text. Can you give advice on this, any help would be much appreciated!
Thanks!

SAMPLE
AIR TAXI - An aircraft operator who conducts operations for hire or
compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer
passenger seats and a payload capacity of 7,500# or less. An air taxi
operates on an on-demand basis and does not meet the "flight scheduled"
qualifications of a commuter.
AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority
to promote the safe, orderly, and expeditious flow of air traffic.
AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
air/ground communications, visual signaling, and other devices to provide ATC
services to aircraft operating in the vicinity of an airport or on the
movement area. Authorizes aircraft to land or takeoff at the airport
controlled by the tower or to transit the Class D airspace area regardless of
flight plan or weather conditions (IFR or VFR). A tower may also provide
approach control services (radar or non-radar).
ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with
a layer (approximately 5.5% thickness per side) of high-purity aluminum,
popularly used in airplane manufacture.
ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
radio, this is the current NATO version in global use:

Text to columns
 

=left(a1,find("-",a1)-2)
=right(a1,len(a1)-find("-",a1)-1)

Regards,
Fred

"LeisaA" wrote in message
...
500+ sentences, I need to seperate (text to columns) after the first "-"
only. There are subsequent "-" but I only want it seperated at the first.
And when I seperate it in the second column I do not want a space
preceeding
the text. Can you give advice on this, any help would be much
appreciated!
Thanks!

SAMPLE
AIR TAXI - An aircraft operator who conducts operations for hire or
compensation in accordance with FAR Part 135 in an aircraft with 30 or
fewer
passenger seats and a payload capacity of 7,500# or less. An air taxi
operates on an on-demand basis and does not meet the "flight scheduled"
qualifications of a commuter.
AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate
authority
to promote the safe, orderly, and expeditious flow of air traffic.
AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
air/ground communications, visual signaling, and other devices to provide
ATC
services to aircraft operating in the vicinity of an airport or on the
movement area. Authorizes aircraft to land or takeoff at the airport
controlled by the tower or to transit the Class D airspace area regardless
of
flight plan or weather conditions (IFR or VFR). A tower may also provide
approach control services (radar or non-radar).
ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad
with
a layer (approximately 5.5% thickness per side) of high-purity aluminum,
popularly used in airplane manufacture.
ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
radio, this is the current NATO version in global use: