# 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.
###### Who is Participating?

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.

Commented:
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"))
``````

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

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...
Author 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
Commented:
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"))
``````

Saurabh...
TEST_110315.xlsx

Experts Exchange Solution brought to you by