How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1844
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Hi, Could somebody help me please with an Excel spreadsheet

This answer was rated:

Hi,

Could somebody help me please with an Excel spreadsheet formula?

Thanks.

Nick

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

what are you looking to do with a formula?

Customer:

Hi Andrew. Thanks for responding so quickly.

The-PC-Guy :

sure

Customer:

We rent out properties in France and would like to set up a formula to track the numbers of guests from each country.

Customer:

I have been trying to do this with a COUNTIF formula.

Customer:

So for example, =COUNTIF(H3:H54,"American")

Customer:

This works okay but does not help with numbers

Customer:

So, I have the nationality in row H and in row G a number

The-PC-Guy :

are you trying to count where the specific text occurs

The-PC-Guy :

can you provide a sample sheet

Customer:

Sure

The-PC-Guy :

if you click the paperclip icon at the top of the chat

The-PC-Guy :

it says image

The-PC-Guy :

but you can post an excel file the same way

The-PC-Guy :

after clicking paperclip

Customer:








































































































Guest Name SourceNumbersNationality
Bertha French Owners Direct5American
Natalia SamoilenkoOwners Direct6Russian
XXXXX XXXXXVRBO4American
Mary Lou BoutwellMary Maffia3American
The-PC-Guy :

so are you looking for a total of the numbers

Customer:

Yes.

The-PC-Guy :

so if there are 3 american values

The-PC-Guy :

you would want the result to be 12

The-PC-Guy :

not 3

The-PC-Guy :

is this correct

Customer:


































American0
Australian1
British5
Russian2
Danish1
South African1
New Zealander1
Canadian1
Customer:

Sorry!

Customer:


































American0
Australian1
British5
Russian2
Danish1
South African1
New Zealander1
Canadian1
The-PC-Guy :

ok

The-PC-Guy :

i think i need to see the actual sheet to make this work for you

The-PC-Guy :

also will give me a better understanding

The-PC-Guy :

if I can see the exact setup

The-PC-Guy :

can you try the paperclip option

Customer:

Okay - no problem.

The-PC-Guy :

to post the actual sheet

Customer:

Yeah, sure. Just spotted it!

Customer:

Did you get that ok?

The-PC-Guy :

yup

The-PC-Guy :

give me a min to open

Customer:

It's not the full spreadshett I'm afraid as there's a lot of sensitive financial info in a lot of the cells - sorry!

The-PC-Guy :

no

The-PC-Guy :

problem

The-PC-Guy :

just as long as I get an idea

The-PC-Guy :

i can give you the correct formulas

Customer:

Thanks!

The-PC-Guy :

gonna take me a minute to open anway

The-PC-Guy :

still firing up excel

Customer:

no problem!

The-PC-Guy :

got it

The-PC-Guy :

so where is the problem ocuring

The-PC-Guy :

I see cells B59

The-PC-Guy :

- B66

The-PC-Guy :

seam to have those formulas

The-PC-Guy :

those values look correct to me.

The-PC-Guy :

unless you were looking for different values

Customer:

In think I got the COUNTIF formula right, but I wasn't sure how to multiply.

The-PC-Guy :

ok

The-PC-Guy :

what are you multiplying

The-PC-Guy :

what values x what values

Customer:

So, in the example of C3, we had 5 Americans.

Customer:

Can I multiply x5 and get it to come up below?

The-PC-Guy :

gotya

The-PC-Guy :

yes

The-PC-Guy :

try this formula

The-PC-Guy :

put in

The-PC-Guy :

cell

The-PC-Guy :

B59

The-PC-Guy :

=SUMIF(D$3:D$54,A59,C$3:C$54)

The-PC-Guy :

then copy and paste that to

Customer:

Ok

The-PC-Guy :

B60 - B66

The-PC-Guy :

or however many you need

The-PC-Guy :

lemme know how it works out for you

Customer:

I get 51 Americans in total which is spot on - great!

The-PC-Guy :

greate

The-PC-Guy :

please don't forget to rate

The-PC-Guy :

bonus also appreciated

Customer:

Would that formula work with all the cells and nationalities?

The-PC-Guy :

yup

The-PC-Guy :

just copy and paste the formula

The-PC-Guy :

down to b60

The-PC-Guy :

- b66

Customer:

Excellent - many thanks for your help!

The-PC-Guy :

or however many rows you need

Customer:

Great!

Customer:

Much appreciated!

The-PC-Guy and other Microsoft Office Specialists are ready to help you