[Solved] How use COUNTA with a date range

[DaveInDenver]

I can do a COUNTA in a range (Column B) that contains text and nulls. (easy)

I want to COUNTA in a range (Column B) that contains text and nulls (same as above) IF a date in Column A (same Row) is within a given MM/YY.
So how do I include an IF function, or a COUNTIF function, and/or MONTH() into my formula?

Note: I would rather not have to set a bunch of start-date end-date cells all over my multi sheet document... I need to do lots of MM/YY COUNTA's on text in multiple vendor columns. I have seen SUMPRODUCT... would need to create and manage too many start-date end-date cells.

Thanks.

[Villeroy]

This is not very easy to do in a spreadsheet because it is a 100% database task.
In a spreadsheet you need to take care that your dates are formatted numbers rather than text. Value highlighting (Ctrl+F8) can answer this question. With the right data types and the right table layout there are many, many different ways to simulate some database functionality based on months, years, quarters and other categories.

A data pilot can aggregate counts, sums, averages, min, max etc for all months, years, quarters and other categories without writing a single formula.
There is the row filter utility which can be combined with function SUBTOTAL to calculate based on visible cells ignoring the hidden ones.
There are D-functions (DSUM, DCOUNT, DAVERAGE etc) which may be combined with a so called "advanced filter" using the same criteria range.
You mentioned SUMPRODUCT which works in a somewhat awkward way.
Any of these approaches requires correct data types. Most of them require a normalized table layout.

[keme]

I don't think you need to add a lot of extra fields. See the attached fragment.
SUMPRODUCT() formula in cell D1 counts records where column A holds a date in the current month and column B is non-blank. For a different month, substitute the NOW() function calls with a different value.

Cheap trick: The third parameter to the DATE() function takes 1 as the first day of the given month. Entering day 0 yields the last day of the month before.

I agree fully with Villeroy's comments about data integrity and such, though. Be aware of the pitfalls of this kind of prototype.

[eremmel]

You can do some smart things with SUMPRODUCT. I made an example for you. I used a named cell for the reference date called 'MonthYear'. The dates in column A are named 'Dates'. Above the vendors the hits are mentioned. I used SUMPRODUCT with boolean-vectors:

Code: Select all

=SUMPRODUCT(YEAR(Monthyear)=YEAR(Dates),MONTH(Monthyear)=MONTH(Dates),ISTEXT(B5:B21))

You can also play with named ranges and or named formulas (Menu: Insert->Names). Check columns C and D. The formula comes down to in column D:

Code: Select all

=SUMPRODUCT(InYear,InMonth,rVendorHasText)

Note that the I use the 'r' in 'rVendorHasText' to indicate a relative forumla/range
Instead of ISTEXT() you can also take an other information-function.

[DaveInDenver]

Yea!
Good intel. I'll give them a try.
Yep... data integrity and cell formatting standards are a given for this kind of formula work.

Villeroy... yep... this is database logic... my DB past is creeping in to the present.
Keme... Yea... thanks for example, tips AND a functioning formula!!!
Eremmel... multiple suggestions... too smart. I did not see a formula in the example... did I miss it?

[eremmel]

What do you consider a formula?

[Villeroy]

Eremmel... multiple suggestions... too smart. I did not see a formula in the example... did I miss it?

The cells with red background color are sumproducts.

[JohnSUN-Pensioner]

Perhaps my suggestion may seem strange. But I would venture to express it. As you look at the use of Data - Subtotals? Format the date as a month - year and apply the tool Subtotals with the calculation of counts.
See third example in this topic

[eremmel]

Hi JohnSUN, I like your suggestion, and tried it with my example3. I found out that the data has to be sorted, and that the 'auto sort' will break results of inter-row relations. But the nice thing of your suggestion is that one get all counts for all month-years at once!

[DaveInDenver]

Hi all,
I've got it!

Used a combination of a few suggestions. I used SUMPRODUCT for the solution.

My client will will have to propagate this this over both time and expansion of their vendors so I had to scale back the use of named ranges and dynamic date windows and so build it with something (I hope) they could manage. And I need delivery BOD Monday so I did not have time (talent?) to run multiple solutions.

=SUMPRODUCT(NOT(ISBLANK($'Customer - Appt & Source'.$K$5:$K$39));$'Customer - Appt & Source'.$E$5:$E$39>=C$3;$'Customer - Appt & Source'.$E$5:$E$39<=C$4)

K = text (or not) in vendor column (lots of Vendor columns)
E = date column (just one date column)
C3 = start date
C4 = end date

YEA!

Thanks to all you brainy people!
Dave

[Villeroy]

Aggregations.ods

Adv. filter with SUBTOTAL, SUMPRODUCT, DSUM, DCOUNT, DAVERAGE and a data pilot

(58.09 KiB) Downloaded 657 times

Yet another version of my database-in-spreadsheet demo with numbers to count, sum, average by categories and date intervals using advanced filtering with SUBTOTAL, SUMPRODUCT, DSUM, DCOUNT, DAVERAGE and a data pilot.