count function

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Count function

Count function

resolvedResolved · Urgent Priority · Version 2003

Tally has attended:
Excel VBA Intro Intermediate course

Count function

Hi

I have a formula that does not appear to work... basically if "EOD" appears in a range and the word "Yes" appears in the column next to it then count.

=COUNT(IF(C3:C58,C6),IF(B3:B58,B1))

c6 = eod
b1 = yes

I get 0 and when there is a instance where there is EOD and a yes next to it... it still appears as 0.

Pls help...

Tally

RE: count function

Hi Tally

Thanks for your question.

Do you want Excel to count when eod and yes appear in the same row (e.g. say yes is in B1 and eod is in C1), or if eod appears in one range (e.g. B1:B50) and yes appears in an adjacent range (e.g. C1:C50) but they are not necessary in the same row.

thanks
Amanda

RE: count function

EOD is in one column...c3:c58
'Yes' is the column before b3:b58

c3 holds the value EOD

and b1 holds the value is yes

thanks
T

RE: count function

What do you want Excel to count?

RE: count function

Every instance where there is eod with a yes along side it...

Tally

RE: count function

Hi Tally

I will attach an example file to see if this answers your question - basically what the formula is doing is looking in the 'database' area (B4:C29), for the criteria specified in the criteria area (which is the word yes and the word eod in the same row), and count every time this occurs within the database.

If you are using the DCOUNTA function then it is important that there are headings above the columns containing 'yes' and 'eod'; and these headings are also included in the criteria.

Amanda

Attached files...

dcount.xls

RE: count function

Hi

Can i send you the spreadsheet? Tal

RE: count function

I've just attached the file - see what you think :)

RE: count function

Hi Amanda

My spreadsheet is like this:

Subscribed? Template for service
Yes EOD
No Time Series
No All Premium
No Intraday
No Terms & Conditions
No Symbol Cross Ref
No Corp Act
No Corp Act
Yes EOD
No Time Series
No All Premium
No Intraday
No Terms & Conditions
No Symbol Cross Ref
No EOD

so the total should be 2... i have tried your formula... but i do not know what the "a" is for...

Tally

RE: count function

it works....

Thank you very much...:) your a star

RE: count function

Great, I'm pleased to hear that

Have a good weekend :)


 

Excel tip:

Separate the year from a date

To separate the year from a date use the =year() function, eg a date is in cell A1 and in A2 you wish to display the year enter the function =year(A1)

View all Excel hints and tips


Server loaded in 0.08 secs.