A

#### Astro

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

You should upgrade or use an alternative browser.

A

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

T

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

H

....Teethless mama said:=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)

B

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)

H

Bob Phillips said: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. ....

Yeah, but

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

should be faster.

B

--

---

HTH

Bob

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

H

has 1 of 4 words entered. I need to count the number of times a word begining

w/ 'D' is entered per month.

Anyone?

H

word entries. I need to count the number of times a word begining with 'D'

was entered per month.

Any help is appreciated. Thanks

T

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

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

H

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

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

C

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)

H

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?

T

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

L

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:

F

=left(a1,find("-",a1)-2)

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

Regards,

Fred

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

Regards,

Fred

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.