[Solved] countif syntax

[digitus]

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.

[Hagar Delest]

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 ?

[digitus]

IF

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.

[Hagar Delest]

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

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

[digitus]

Test calc.ods

ods. file

(10.64 KiB) Downloaded 539 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.

[Hagar Delest]

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

=SUMIF(D5:D19;"a";E5:E19)

[digitus]

Thanks for that amendment. sumif works now. I'll edit my first post to solved.