98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Count function
Count function
Resolved · 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...
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 :)
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Separate the year from a dateTo 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) |