count multiple values in rows & show as quartely figuers

Hi.

Where are the results to go? Have you already set up a table ready to house them?

Regards

Ah. I'd be happy to attempt to generate the results for you, but unfortunately I'm not prepared to set up the table to house those results. Besides, a prepared table could also be filled in with a couple of expected results, which would be a real help when working on a solution.

Regards

Well, for what do you want results? Surely you know that much?

Regards

Originally Posted by swk8429

how to affiliate a date/Quarter to them.

Sorry - not sure what this means.

As I said, if you can set up some tables ready to house the returns, then I'll be happy to give you the formulas that generate those returns. At the moment, however, without those tables and without you providing any manually-calculated results, I have no idea what the results should be, nor where they should go.

Regards

Hi,

please see updated workbook with tables. I have manually typed in the results to show what I was hoping to achieve.

I hope it makes more sense now.

Thanks

Thanks a lot.

So all results take their data from the REFERRAL RECEIVED sheet?

And there's only ever one line each for QTR 1, QTR 2, etc.?

Regards

Does any solution have to take into account e.g. the Allocation Date (7-Mar-2014 = QTR1, etc.)? Or will QTR1, for example, always be in row 11, QTR2 always in row 12, etc., and so I don't need to worry about looking up dates?

Regards

And are those dates fixed? Is there always only one row for QTR1, one row for QTR2, etc.?

Regards

Ok, so would it be possible for you to re-upload your workbook with a more realistic example? It's a little difficult at the moment as I'm still unsure on how many different rows there could be for each quarter.

Regards

Great. Thanks. Are the dates for each quarter listed anywhere in the workbook? Will need to reference them in the formulas.

Regards

But then how will I tell the formulas which lines belong to which quarter?

Regards

Put them anywhere you like. I suggest a simple three-column table with QTR1 to QTR 4 in the first column, followed by the Start and End Dates for each.

Regards

Says it's password-protected?

Regards

Thanks.

Re the dates, am I using column C - Allocation Date - to check which quarter each belongs to?

I just did a quick check in the table against your first expected result in the NEEDS table, i.e. for "ACEDU" in QTR1. You have put 5 as your expected result, though I can only see 3 entries where the Allocation Date is between 01/01/2015 and 31/03/2015 and the column N entry - Needs - contains the string "ACEDU": rows 19, 26 and 29. Can you clarify which 2 I've missed?

Regards

Ah, I see. Then could I ask for perhaps just three random expected results, manually determined by yourself - maybe one each for NEEDS, OUTCOMES and TARGETS - so that I can compare my formula results against them and so know that I'm on the right track?

Regards

Originally Posted by swk8429

Needs 2014/2015

results for "acedu"

qt1 5

Ok. I'm a bit worried. I've only checked your very first expected result, but already I don't understand how you get 5.

I filtered column J - Referral Rec Date - for between 01/01/2015 and 31/03/2015 inclusive (as per your dates given for QTR1) and then filtered column M - Needs - for anything which contains the string "ACEDU" and I only get 4 results, not 5, i.e. rows 19, 26, 29 and 39. Can you clarify which one I've missed?

Originally Posted by swk8429

the colum qtr belongs to is different its column x sorrry if this complicates things

So which columns do I use for each of the tables: NEEDS, OUTCOMES, TARGETS, REF INT and REF EXT? Column J or column X?

Regards

The names you gave to the Quarters in the DATES sheet that you set up don't quite match those in your tables, so you'll have to remove the "R" in each (i.e. change "QTR1" to "QT1", etc.) so that they do.

After that, the formula for cell B5 of the NEEDS sheet would be:

=SUMPRODUCT(0+('REFERRAL RECEIVED'!$J$11:$J$39>=INDEX(' DATES'!$B$4:$B$7,MATCH(B$4,' DATES'!$A$4:$A$7,0))),0+('REFERRAL RECEIVED'!$J$11:$J$39<=INDEX(' DATES'!$C$4:$C$7,MATCH(B$4,' DATES'!$A$4:$A$7,0))),0+(ISNUMBER(SEARCH(","&$A5&",",","&'REFERRAL RECEIVED'!$M$11:$M$39&","))))

Copy down and to the right to fill your table (apart from the TOTAL column, of course).

Note that this formula relies on each of the entries within your delimited lists being separated consistently, i.e. by ",". You currently have some inconsistencies in there, in that some entries are separated by ", " (comma followed by a space) and some by "," (comma but no space), e.g.:

ISO,LANGB, NTA,NRTPF

in which it is not clear why LANGB is followed by a comma and a space though the rest are followed by a comma only.

This will need to be standardised in order for the formula to give correct results.

Also, sometimes the last entry in a list is followed by a comma; sometimes not, e.g.:

HOU, LANGB,

but:

CFLIB,SUBM

My formula will not work unless the last entry does not have a comma after it. I can change this if you want, but, either way, you need to be consistent in your data, so that all of your entries follow precisely the same pattern. I would recommend removing all trailing commas, as that it is standard practice when making lists.

Also note that, although you can of course amend the upper row being referenced in the formula I gave (currently 39) to account for larger datasets, you should be careful not to make it too large (and certainly don't reference entire columns!). Every cell you reference, whether empty or not, will cost extra calculation, so try choosing a suitable upper bound, e.g. 100, which is guaranteed to always be sufficient, though at the same time never too large.

I will leave it to you to adapt this formula so that it works for the other tables. This will simply require amending the columns being referenced where appropriate.

Regards

Drag your formula bar down so that it covers about half your window and you'll see that you appear to have pasted the formula in at least three times in every cell, not just once.

Regards

Great!

Cheers