I have a spreadsheet that keeps track of (for example) every time someone enters my store. Column A is the last name and column B is the first name, (with other data entered in subsequent columns). I entered the following formula into column Q:=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)
(I chose till row 1000 because I knew I won't exceed that amount of rows anyway.)
My question is, how can I modify the formula for it to meet the following two criteria:
- Categorize the customer by category (which is manually entered in column C). IOW if two customers with the same name are entered it will count them separately due to the difference in category; and,
- It should only count how many times a given customer entered the store within the last two weeks, or any other specified amount of recent time. (Dates on which customers visited are also entered manually in a separate column.)
TIA
Just add more criteria into the COUNTIFS. The first item (category) would certainly be a simple extension of what you have already. And the second item (time-frame) would be similar, but you'd end up repeating the condition. For example:
Place the category in Column C, and the date of entry into column D. Your new formula in Column Q would then be:
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2,$D$2:$D$1000,">="&?x?,$D$2:$D$1000,"<="&?y?
where the ?x? represents the beginning of the time-frame you're looking at, and the ?y? represents the end of the time-frame you're looking for. Ovbiously those values would be placed in a fixed spot off to the side somewhere.