Count or Countif?
I have attached an example of a spreadsheet whereby I am trying to count the number of "x's" in Column B based upon the "department" in Column A. I have tried the Count, CountIf and CountIfs functions but cannot get it to work. Do I have to be concerned about how the cells are formatted? Can anyone provide with me a solution?
Example.xlsx
Last edited by thomcatbob; 09-24-2011 at 11:02 PM.
Re: Function to use in Excel 2007? Count or Countif?
just use the pivot table optionOriginally Posted by thomcatbob
you will get the result.
see attatchment
is it what you need?
Azam
If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
For prompt answer, be descriptive, concise, short, direct, and to-the-point.
Re: Function to use in Excel 2007? Count or Countif?
You obtained the correct answer for Day 1, but I copied the function into my spreadsheet and tried to total by day 1 and 2. I obtained the correct result for day 1 but day 2 was incorrect. Please note each column represents a day and the number of 'x's' by dept are going to be different.
Did I copy and paste incorrectly?
Here's my spreadsheet with your function:
The formula does not look at either column B or C. The fact that it counts correctly for day 1 is pure coincidence, since day 1 has x values in all rows.
Use this in B14 instead
=SUMPRODUCT(--(--LEFT($A$2:$A$11,1)=$A23),--(B$2:B$11="x"))
copy down and across.
Thanks teylyn - it performs the function I needed!!!
Can you explain how it works? I understand most of the function except the --LEFT - what does this mean?
Hi,
=left(<cell>,n)
takes returns the a string of n characters from a text string in <cell>, starting from the left. The -- before the Left is a typo, which I did not notice when I copied the formula. But it does no harm, so the formula still works.
The solution provided by "teylyn" answered my question for counting the "x's" in each column. Now I'm trying to figure out how to "count" the "x's" across the rows, e.g. Total E2 = number of "x's" in Columns B-D. Since I'm using an "x", the function count and sum aren't working since it's not a number.
Hello,
in E2
=COUNTIF(B2:D2,"x")
copy down.
cheers,
Thanks teylyn for your responses - they have worked like a charm.
My requirements have changed a bit - I've had to add a column and need to total the "x' values by Breakfast (b) and Lunch (L). Attached is the sample worksheet.
Anyone have any suggestions?
How about:
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
ConneXionLost - thank you, it worked.
Now, I'm trying to get the grand totals by columns B&L utilizing the numbers rather than the 'x's'. Would I use a variation of that formula? I'm still grappling with the SUMPRODUCT function.
Re: Count or Countif?
I think I got it!!!!
=SUMPRODUCT(--(($C$4:$AP$4)=AQ$4),--($C23:$AP23))
Many thanks to everyone who have helped out on these functions!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks