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).
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.
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;_)
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.
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.