Excel help

Fed up talking videogames? Why?
User avatar
Slayerx
Member
Joined in 2008

PostExcel help
by Slayerx » Mon Sep 25, 2017 4:12 pm

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.

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel help
by rinks » Mon Sep 25, 2017 6:14 pm

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

Loves us all since 2008
User avatar
Slayerx
Member
Joined in 2008

PostRe: Excel help
by Slayerx » Mon Sep 25, 2017 7:12 pm

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.

User avatar
Slayerx
Member
Joined in 2008

PostRe: Excel help
by Slayerx » Mon Sep 25, 2017 7:25 pm

Image

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.

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel help
by rinks » Mon Sep 25, 2017 7:42 pm

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".

Loves us all since 2008
User avatar
Slayerx
Member
Joined in 2008

PostRe: Excel help
by Slayerx » Mon Sep 25, 2017 9:01 pm

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.

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel help
by rinks » Mon Sep 25, 2017 9:07 pm

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

Loves us all since 2008
User avatar
Slayerx
Member
Joined in 2008

PostRe: Excel help
by Slayerx » Mon Sep 25, 2017 10:31 pm

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 :)

User avatar
Slayerx
Member
Joined in 2008

PostRe: Excel help
by Slayerx » Tue Sep 26, 2017 3:07 pm

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 :)

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel help
by rinks » Tue Sep 26, 2017 3:34 pm

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.

Loves us all since 2008

Return to “Stuff”

Who is online

Users browsing this forum: floydfreak, Garth, Godzilla, Grumpy David, Met, Ploiper, shy guy 64, TonyDA and 357 guests