SUM/INDEX/LEN with date

[jimbosi]

Hello everyone,

I'm using the following formula to extract a count of multiple values in a cell:

=SUM(INDEX((LEN($A$1:$A$100)-LEN(SUBSTITUTE($A$1:$A$100,H2,"")))/LEN(H2),0,0))

I need to add a portion to the formula that looks for a date range in E:E (which are formatted MM-DDD-YYYY), specifically month to month. I'm not sure if a date criterion should go at the end of the formula, or in it. I'm used to using COUNTIFS, which are simple by comparison. I'm still learning more complicated functions, so apologies for submitting a question that probably has an easy answer (that I haven't found yet).

[XOR LX]

Hi,

Assuming your date is in I2:

=SUMPRODUCT(($E$1:$E$100=I2)*(LEN($A$1:$A$100)-LEN(SUBSTITUTE($A$1:$A$100,H2,"")))/LEN(H2))

Regards

[jimbosi]

Thank you for such a quick reply. To explain further, I have a date column in E:E. Records come in, and I use the formula I included to count instances of a string in A:A. Now I need to do that on a monthly basis, meaning between two dates (1-31 Jan, for instance), count each instance of a string in a cell. I envision it being something like DATE >=2014,1,1 & DATE <=2014,1,31. Of course, that's not correct, and I'm uncertain where it would go in relation to the rest of the formula. I have a feeling it's some permutation of what I have placed where you put ($E$1:$E$100=I2)-- I just don't know enough syntax. Thanks again for your help.

As an aside, the rest of my sheets only have one value in a cell, so I use COUNTIFS. That's not really achievable here (and I can't really use wildcards).

[XOR LX]

In that case I think you'll really need to upload an actual workbook example, with your desired result clearly outlined. Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards

[jimbosi]

A:A has the strings I want to count. B:B contains the dates. H:H are the reference strings I want to have counted, and I:I is where I'm placing the aforementioned formula. I want to divide those columns by date (Jan, Feb, and so on). Thanks again.

ExcelForumHelp.xlsx

[XOR LX]

Thanks. In which cells are your desired results to go?

Regards

[jimbosi]

"Number of Cites" I:I, would be transformed into date columns; I:I=Jan, J:J=Feb, K:K=Mar and so on. Thanks.

[XOR LX]

Ok, then presuming you enter the appropriate (Excel) 3-letter abbreviations (Jan, Feb, Mar, etc.) for the months in I1, J1, K1, etc. this formula in I2 and then copied across and down:

=SUMPRODUCT((TEXT($B$1:$B$100,"mmm")=I$1)*(LEN($A$1:$A$100)-LEN(SUBSTITUTE($A$1:$A$100,$H2,"")))/LEN($H2))

Regards

[jimbosi]

Excellent. Unfortunately, I get zeroes all the way down. I1 is entered as "Jan 2014" and it displays as "Jan-2014". I entered your formula on the sheet I provided. I must be doing something wrong, so I'm including the modified sheet. Thanks!

[XOR LX]

Ah. In your last spreadsheet you had just Jan, Feb, etc. (no years).

Try:

=SUMPRODUCT((DATE(YEAR($B$2:$B$100),MONTH($B$2:$B$100),1)=I$1)*(LEN($A$2:$A$100)-LEN(SUBSTITUTE($A$2:$A$100,$H2,"")))/LEN($H2))

Regards

[jimbosi]

Bam! Perfect! Thanks for all your help. Problem solved. Cheers.

[XOR LX]

No worries! Glad I could help.