Excel Fomula Count Question

Hello,

I have a report I need to add something to. I have an employee sheet that is already showing the dollars grossed each month for each individual, but I want to get a little more in depth. I want to make it so a certain part of an invoice (specifically the first word only) is what I am looking for in order to logic off of. Here is an example, Sheet 1 is where the employees are and Sheet 2 is my data. For Person 1 I want the dollar amount added up for anything with the first word being WG. The data I want to pull is the dollar amount added up (the sum of all WG's for this individual) which is next to the WG in its own column.

Sheet 1, a cell for the formula next to collector
Sheet 2 (columns), Employee ID, Amount, Payment Type.

Thank you.
WO2015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Okay Assuming..

You want your results in sheet1 from sheet2 you can use this formula ...

=SUMPRODUCT((Sheet2!A2:A500=Sheet1!A1)*(Sheet2!B2:B500)*(LEFT(Sheet2!C2:C500,2)="WG"))

Open in new window


Now this is what i have assumed...

In A2:A500 you have employee id match..
In B2:B500 you have the amount of the dollar which you want to sum
In C2:C500 in payment type you will have WG which is basically the sum of what you are looking for.. as in the first 2 characters...

Please adjust your range as per your data..

Also if this doesn't work for you then can you post your sample workbook as it will become easy to write a formula for you accordingly..

Saurabh...
WO2015Author Commented:
Hello,

Thank you. I edited the formula you had to match my columns but when I put the formula in and press enter its asking for me to pick a document and doesn't do anything.

=SUMPRODUCT((Sheet2!M2:M500=Sheet1!A3)*(Sheet2!E2:E500)*(LEFT(Sheet2!I2:I500,2)="WG"))

I have attached a test file that is generally similar (the columns are the same) with all of the content missing except for what we are working with.
TEST_110315.xlsx
Saurabh Singh TeotiaCommented:
Your sheet names are different then what you told me..Use this formula..

=SUMPRODUCT(('All Payments'!$M$2:$M$15000=A3)*('All Payments'!$E$2:$E$15000)*(LEFT('All Payments'!$I$2:$I$15000,2)="WG"))

Open in new window


Enclosed is your workbook only..

Saurabh...
TEST_110315.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WO2015Author Commented:
Perfect, thank you! Sorry, I am so used to using Sheet1 and Sheet2.
Saurabh Singh TeotiaCommented:
You are welcome...It's okay..Always Happy to Help.. :-)

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.