Only count cells with any one of each of several separate values, on dates before today

I've got dates heading up columns, ranging from past to future containing data for the year to date.

The number of instances of certain values on each date and on all dates cumulatively, needs to be counted as the data appear, moving through the year.

So far, the formula used has been

=COUNTIF(F3:F6,"ball")

But this returns zero values for future dates, which skews the averages.

What formula will only count the values on dates before today such that as soon as new data arrives for each date, the counts will change and the new percentages and averages be calculated?

Then the averages formula can calculate only cells with values. I'd appreciate help with that as well. Confused with syntax, trying to use IF, COUNTIF and other functions that I can't seem to make work in combination.

Including a picture as well, so you can see at a glance before opening the also attached worksheet.

excelprob.jpg

countifs()
averageifs()

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Not sure, does this go some way to a solution

Many thanks Wayne. It seems the countifs is working, and leaving the percentages as simply =IF(F10="","",F10/4) does the trick once the countifs make for the needed blank cells.

But I'm confused; why COUNTIFS and that outlier B10? I tried making it simply COUNTIF with the range F3:F6 and there was a 'too few arguments' error.

What am I missing?

countifs() allows more than 1 criteria , so we can check the dates as well

the B10 - looks at the criteria , so BALL
you can go back to using "Ball" if prefer
Just by using the B10 - then if you change that column for different criteria , you dont need to type it in
BUT
=IF(C$2<=TODAY(),COUNTIF(C$3:C$6,$B10),"")
COUNTIF works as there is only 1 criteria

Also using the "" in dates for the future
Then average should also now work rather than averageifs()
As instead of zero we now have the blanks

lots of different ways to approach
Here is the IF(using a blank ) using a countif & as now blank you can change to average()

Originally Posted by etaf

countifs() allows more than 1 criteria , so we can check the dates as well

the B10 - looks at the criteria , so BALL
you can go back to using "Ball" if prefer
Just by using the B10 - then if you change that column for different criteria , you dont need to type it in
BUT
=IF(C$2<=TODAY(),COUNTIF(C$3:C$6,$B10),"")
COUNTIF works as there is only 1 criteria

Also using the "" in dates for the future
Then average should also now work rather than averageifs()
As instead of zero we now have the blanks

lots of different ways to approach
Here is the IF(using a blank ) using a countif & as now blank you can change to average()

Yes, I see. Rookie had forgotten that the formula contains a cell reference and not a value, unless said value is wrapped in double quotes.
Anyway I have plugged your formula into all future and past dates, and all is working well.

I'm going to recreate part of the actual table and post it here in hopes it may assist someone else someday.

The formula I've started with is

Please Login or Register  to view this content.

, attempted to be adapted from your

Please Login or Register  to view this content.

From there it's been copied throughout the table. Set the absolute cell refs correctly, it seems.

I had trouble with this; it seemed the formula wasn't working on being pasted into cells under past dates.

By chance I stumbled on the solution; in anonymising the Example table I changed the dates. Doing this caused a change in the formatting I don't quite comprehend, as they were already in 'date' format...but anyway the look of the dates in the date row changed from d/m to dd/mm/yy, and after this the formula worked in all past dates.

Thanks, etaf! Yam ruoy etaf eb yppah.

PS there are handy sites that generate random lists of names or other words. That makes it easy to fill out a table with anonymous data. Search for 'random list of names/words', or 'random name/word generator'.

Glad its all now resolved for you

DEAR Wayne,

After 6 months I've returned to this post and your answers to mine it for your second solution: the AVERAGEIFS one.

I don't know how to thank you. You knew it would be needed!

What an absolute champion, etaf.

you are welcome