Ask Your Question
0

count entries in named column of a range [closed]

asked 2013-03-15 09:40:04 +0200

RossMunro gravatar image

I have a standard spreadsheet with Jan, Feb, Mar etc headings for the whole year. I want to count the number of entries under the "Mar" heading, for example (within a defined range).

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-26 21:46:07.929744

Comments

Thanks qubit, but counta() doesn't seem able to recognise column headings. For example, I need to go along a row until I find "Mar" (say), then count the empty cells in that column. I'm trying to figure out dcounta(), but an example would be a great help.

RossMunro gravatar imageRossMunro ( 2013-03-15 14:49:23 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2013-03-16 05:09:50 +0200

RossMunro gravatar image

Thanks for the help to all who responded. I've gone with dcounta(), although the result lacks elegance, it works. One of the formulas looks like this =DCOUNTA(December,"Fri",Frid) + DCOUNTA(December,"Sun",Sund), where "December" is a calendar array with "Mon", "Tue", "Wed" ... etc headings. Mond, Tues, Weds etc are ranges defining search criteria (<>). All I wanted was to calculate how many Fridays and Sundays there are in each of the months of the year.

ps. Because of the search criteria stipulating non-empty cells, I realise that DCOUNT() will do as well (no need to search for non-empty cells twice;_)

edit flag offensive delete link more
0

answered 2013-03-15 09:56:33 +0200

qubit gravatar image

Hi @RossMunro,

Sounds like 'COUNTA' might work for you:

=COUNTA(B2:B18)

The Online Help provides some information about this function:

COUNTA
Counts how many values are in the list of arguments. Text entries are also counted, even when they contain an empty string of length 0. If an argument is an array or reference, empty cells within the array or reference are ignored.

edit flag offensive delete link more
0

answered 2013-03-15 22:55:09 +0200

m.a.riosv gravatar image

Hi RossMunro,

Take a look to the attached file, to see if do what you want.
SelectColumnToSum(Offset).ods

The formula: =SUM(OFFSET($A$2:$A$4;0;MATCH("May";$B$1:$M$1;0)))

$A2:A$4 is the pattern of the range to sum.
$B$1:$M$1 are the month labels.

With MATCH() we find how many columns we need to move the column pattern to get the column with the wanted month.

If months are dates not labels then is needed to adapt the formula in MATCH.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-03-15 09:40:04 +0200

Seen: 1,983 times

Last updated: Mar 16 '13