[Solved] countif syntax

Discuss the spreadsheet application

[Solved] countif syntax

Postby digitus » Sun Dec 02, 2007 12:42 pm

Greetings, I am using Ubuntu 7.10 and have set up a spreadsheet to track sales by various groups of people.
I want to identify the groups with key letters or names in one column with sales returns in another.
Apart from totaling all sales (which is easy with "sum"), I need to record how many from each group
have returned their figures as some do not have anything to enter. I could, of course, set up a separate sheet for
each group, but this leads to other problems. So, I want a single alphabetical list with the data passed to a new sheet.
I can do all I want except when trying to count the number of reports. I have tried "countif" but keep getting error messages
which appear to point to incorrect syntax. I've search help and looked up countif in this forum but without success.
If anyone can help, I'd be very grateful.
Sorry if this post is a bit long but I'm hoping it gives enough detail for you to see my problem.
Last edited by digitus on Wed Dec 05, 2007 10:42 pm, edited 1 time in total.
digitus
 
Posts: 4
Joined: Sun Dec 02, 2007 12:20 pm

Re: countif syntax

Postby Hagar Delest » Sun Dec 02, 2007 3:19 pm

Can you attach a dummy file with your COUNTIF formula so we can check ?
Even the examples given in the OOo help file haven't helped ?
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28621
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: countif syntax

Postby digitus » Sun Dec 02, 2007 9:38 pm

countif_1.jpg
IF
countif_2.jpg
COUNTIF


Thanks for your reply. I hope these attachments are ok. As you can see, I want to separate the returns from each sales group
according to the designator in column D. I should get the number of entries from column E according to the relevant group.
I hope this is clear with the aid of the images.
digitus
 
Posts: 4
Joined: Sun Dec 02, 2007 12:20 pm

Re: countif syntax

Postby Hagar Delest » Sun Dec 02, 2007 10:04 pm

Attaching the .ods file would have been better but never mind, I think I see what's the problem.

For such situation, you need to use the SUMIF function. Replace your code by the following :
Code: Select all   Expand viewCollapse view
=SUMIF(D5:E19;'a')

NB: COUNTIF will only count the number of occurrences, no sum at all : Returns the number of cells that meet with certain criteria within a cell range. (from OOo help file).

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28621
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: countif syntax

Postby digitus » Mon Dec 03, 2007 3:54 pm

Test calc.ods
ods. file
(10.64 KiB) Downloaded 265 times


Thanks for your reply. I regret to say it doesn't work. I tried sumif but got a blank cell. Tried countif but it returns the number of times
"a" appears in column D. I want it to ignore any cell in column E that does not contain a figure. You can see my results at H21 and I21.
In this example the correct answer would be 3. Three cells in E contain amounts two would be ignored.
I've attached the ods file as you suggest (I'm new to this, so sorry if I've been a bit slow on the uptake).
Perhaps my requirements will be a bit clearer now. Anyway, many thanks for your input.
digitus
 
Posts: 4
Joined: Sun Dec 02, 2007 12:20 pm

Re: countif syntax

Postby Hagar Delest » Mon Dec 03, 2007 11:49 pm

OK, my fault. I don't know why I've seen that syntax above (I looked in the OOo help file however !) but the right one is this one :
Code: Select all   Expand viewCollapse view
=SUMIF(D5:D19;"a";E5:E19)
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28621
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: countif syntax

Postby digitus » Wed Dec 05, 2007 10:42 pm

Thanks for that amendment. sumif works now. I'll edit my first post to solved.
digitus
 
Posts: 4
Joined: Sun Dec 02, 2007 12:20 pm


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 42 guests