CODEWITHSUNDEEP
excel
OliEshmade
OliEshmade

Reputation: 121

Counting the number of occurences of a value dynamically

Not sure if I've worded the question correctly... but, I have a spreadsheet that imports data across with a 'transaction date' and on day 1 there may be 15 transactions, day 2 there may be 30 etc.

Now I already have a formula that is counting how MANY are imported each day

=SUMPRODUCT((MONTH('Further Evidence'!$A$2:$A$5000)=MONTH(DATEVALUE(Configuration!H2&" 1")))*('Further Evidence'!$A$2:$A$5000<>""))

That shows how many have come in that particular month, what I need to work out now is what the highest intake was during that month (and if possible, which day it was).

Rather than list 365 days of the year and doing a countif in every cell next to them, is there an intuitive way to only count values that exist in the list?

It will be simple for one of you, but I can't quite figure it out or what to google :)

edit -

=MAX(FREQUENCY('New Appeals'!A2:A5000,MONTH('New Appeals'!A2:A5000)))

This works for the whole list of dates, but how can I make it check months specifically, or pinpoint the specific day?

Upvotes: 0

Views: 69

Answers (1)

Sam Gilbert
Sam Gilbert

Reputation: 1702

To find the max value within a given month you can use an array formula like below

I've used a sample range of rows 36 to 48. I've assumed that date is in column I and that transactions is in column J

=MAX(IF(TEXT($I$36:$I$48, "mmm")="jan", $J$36:$J$48, ""))

(To enter an array formula you have to press ctrl + shift + enter when you are in the cell)

This is restricting the MAX function to the month of jan.

You can then find the day associated to this max value by using another array formula that is a mix of first MATCH then INDEX. The MATCH first looks for the max value within the range of cells associated to the given month, then returns this position. This position is then used in the INDEX to return the date

=INDEX($I$36:$I$48, MATCH(K34, IF(TEXT($I$36:$I$48, "mmm")="jan", $J$36:$J$48, "")))

Please note that if you have two days within a month with the same max then it will just bring back the first one

Hope this helps

Upvotes: 2

Related Questions