GRcade

Bit of a long shot but doing an audit and want excel to do somethings for me just not sure how.

Hoping someone can help

I want it to use a keyword and year and add them up and have it setup so it will continue to add new cases as long as a key word and year is put in a particular field.

So if in a field the key word is Cus and the year is in the format of 17 Excel to add them all up etc.

Hope that makes sense.

Not entirely sure what you mean. Can you provide a screenshot so we can see how the fields are used?

rinks wrote:Not entirely sure what you mean. Can you provide a screenshot so we can see how the fields are used?

Ill do a screen shot.

In a nutshell I will have one column with a number of outcomes and want excel to add each outcome up cumulatively and display a figure.

I found =COUNTIF(range,value) as a suggestion to my problem but not sure if there is anything more robust.

So as above I would like to be able to keep a track how many documents were sent out each year to either a customer or solicitor.

The real data I have has around 10k cases going back around 10 years.

Yeah, COUNTIF is what you need, or more specifically COUNTIFS, which lets you specify multiple criteria. You have to state the range again for each of the criteria, and you can use asterisks as wildcards. So for your example, this should work, counting Customers in year 16:

=COUNTIFS(B2:B17,"*Cust*",B2:B17,"*16*")

i.e. It will count the number of cells (in the B2:B17 range) that contain BOTH "Cust" and "16".

rinks wrote:Yeah, COUNTIF is what you need, or more specifically COUNTIFS, which lets you specify multiple criteria. You have to state the range again for each of the criteria, and you can use asterisks as wildcards. So for your example, this should work, counting Customers in year 16:

=COUNTIFS(B2:B17,"*Cust*",B2:B17,"*16*")

i.e. It will count the number of cells (in the B2:B17 range) that contain BOTH "Cust" and "16".

Thanks I'll give that a try tomorrow

Is there a way to have the range dynamic so if I add more cases to the spreadsheet I don't have to update the end range?

Just thinking for future proofing it.

If you just put B:B with no numbers, it will use the whole of column B, for example.

rinks wrote:If you just put B:B with no numbers, it will use the whole of column B, for example.

Thanks you so much

rinks wrote:If you just put B:B with no numbers, it will use the whole of column B, for example.

Thanks Rinks all worked and it's proving to be very useful

Slayerx wrote:

rinks wrote:If you just put B:B with no numbers, it will use the whole of column B, for example.

Thanks Rinks all worked and it's proving to be very useful

Glad to hear it was ok. Give us a shout if you need anything else.