Help with an Excel query

macsauce

I would be very appreciative if anyone could offer some help with this. I have been searching online generally and also on boards for an answer.

I've tried to simplify my problem into something that doesnt look like the sheet im working with so apologies if it looks dumb.

I have an excel sheet that tracks items, for example payments, that are in date format (there are conditional formats on these to highlight cells based on dates so if possible i would like to keep these in date format. I would like to count the number of first, second, third... payments from each person in the end totals column. Is this possible? can someone steer me in the right direction on this?

Hopefully the image below illustrates my problem better than i have explained it.

Thanks in advance.



Edit: ok the image didnt work. Here is a link to it.

Sheldons Brain

There is no image at present.

How do you characterise the second, third payment etc. Are there different items?
If there are different people and different items then some sort of compound key would distinguish these and you could then count them.

macsauce

Hi Sheldon's Brain, Thanks for taking the time to reply.

I'm not sure i fully understand the question but maybe this helps:

Each row is a person. Column B is "1st Payment", Column C is "2nd" and so on. Each cell is in the date format "DD Month YYYY". Where a person makes a payment, the date of payment is entered. I want to do is count the number of cells in each column that contain a date, any date. At the end of the quarter i'd like to know how many 1st, 2nd, 3rd payments have been reached as there will be a performance metric around it.

I hope this makes it a little clearer!

gleesonger

As mentioned above your question is totally clear.

If you want the number/sum of payments to each person over all time or even constrained to a date range then you need to; create a unique identifier for each person, eg the catonation of their first/last name then use the sumifs or counifs function. See Google.

If you want the above but with the additional constraint of on a particular date eg payments recieved from Bob on a Monday/First day of month, then do as above but create another common with an ID for the day of the week (see Dayofweek function) and then add that to the countifs/sumifs function.

Alternatively use a pivot table, won't update like a formula but you might find it easier.

davetherave

Your actual data might be more complicated than the image you have given but the COUNTA function can be used to give a count of all the non-blank cells( or cells that contain data).
This is assuming that there are no other values other than the date a payment was made present in the column.


Taking your image as an example.
In B6 you would type

=COUNTA(B3:B5)

Which would return 3, as there are three cells in that range with data in them.
The same query in C6 =COUNTA(C3:C5) would return 2 as C4 is empty.

macsauce

Thank you everyone for replying. Unfortunately when it comes to Excel you have to talk to me as if you were explaining it to a labrador. Davetherave, this is exactly what I'm looking for, thank you so much and thanks to all that took the time to reply!