Countif(s) Date-Days, referencing another cell.

[gregoryjb]

Hi all,

Any help with the following would be greatly appreciated.

Essentially, what I'm trying to achieve is a COUNTIFS function, which looks up the number of times a specific client has performed a transaction within 7, 28, 90 and 180 days, as per the attached screenshot. For reference, the client and date columns are within sheet 'Data', with the client name in column BB and the date in column L.

I'm struggling with the date -7 days part of the function/formula?

I'm using cells to provide the dates -7/28/90/180. I would have thought that the following would work for minus seven days (Cell C14) - =countif(data!L:L,">=C7"). As you can see from cell C14 however, this is not prompting the correct outcome, giving me just '1', when there should be a good deal more.

I hope that my explanation is sufficient, but please let me know if you need more information. Once I've cracked this date aspect, I can add in the client element and bump it up to a countifs, which shouldn't be a problem. I'm just struggling to get over this date aspect.

 

[steve the fish]

Use ">="&C7

 

[gregoryjb]

Steve is not simply 'a fish', Steve is a heroic fish.

Thank you so much for this. I remember from previous work that there are occasions where '&' must be used, although I don't know where and why, despite being 'ok' with formulas/functions. I'll have to do some research on this.

Again, thanks very much for your help. The fly on the wall witnessed my clenched fist of success (yours really) as the number updated.

 

[steve the fish]

You are doing it because you are using a cell reference. The way you had it you were using a literal 'is it greater or equal to C7' not the value of cell C7.

 

[steve the fish]

The reason you were getting 1 is no doubt you had a header in there that didnt start with A or B which would make it greater than the literal 'C7'