IF/THEN string in multiple sheets excel

[nakedfool]

hello,
I'm trying to figure out how to do the following:

I have two sheets in one excel document.
SalesRep_42 (one sheet) is where I will be projecting data from RawData (another sheet)

RawData has all the raw data that i exported from our accounting software.

If a cell in column A in the RawData sheet contains the number 42 (sales person's ID number), I would like to add total sales together from row AA (Gross Sales) and have that number displayed in a cell on the SalesRep_42 sheet.

Does that make sense?

This would get me started at least.

Next, I would like to do the same thing except, add another variable in the formula so that i can pull numbers from each month.

[CatEnters]

have you tried SUMIF() and SUMIFS()?

[nakedfool]

no, how would it look typed up? i'm a noob when it comes to excel and really need my hand held when doing some of the complex functions (to me anyway).

=SUMIF(RawData!A=1

thats all i guess to do based on your reply but it's probably way off...

[jeffreybrown]

If everything matches up on the same row between the two worksheets...

=SUMIF($A$1:$A$20,42,'Gross Sales'!$AA$1:$AA$20)

[nakedfool]

Well, the RawData sheet contains all the data, and the SalesRep_42 sheet is blank. I want to pull the data from RawData and only show certain information, such as gross sales and gross profit for sales consultant 42.

I changed up your string a little bit in hopes that it would match my setup, but the value that shows in the cell with the string shows zero "0".

=SUMIF(RawData!$A$1:$A$13724,42,$AA$1:$AA$13724)

Any ideas from here?

Thanks again for the help...

[nakedfool]

ok, this helped me get on the right track, BIG TIME. Thank for the help Jeff.

=SUMIF(RawData!$A$1:$A$13724,42,RawData!$AA$1:$AA$13724)

now, what if I wanted to break this into months? the dates are stored in the format "01-12-2012" or (month-day,year) in row J on RawData?

Does this make sense?

Once I have this, I think I will have everything I need to continue my work.

[jeffreybrown]

Here is what I would do...

In an unused cell, let's make a helper column to extract the month. I used column K.

K1 copied down >> =IF(N(J1),MONTH(J1),"")

Then we can switch to a Sumifs...

=SUMIFS(RawData!$AA$1:$AA$13724,RawData!$A$1:$A$13724,42,$K$1:$K$13724,1)

1 for January, but you can change this out for whatever month you want. You can also put the month in a reference cell and use,

=SUMIFS(RawData!$AA$1:$AA$13724,RawData!$A$1:$A$13724,42,$K$1:$K$13724,$N$1)

I used N1

[nakedfool]

Hi Jeff,
Which Sheet do I put the below formula in (RawData or SalesRep_42)?

=IF(N(J1),MONTH(J1),"")

Thanks,

[jeffreybrown]

You put it in the sheet that holds the date...

[nakedfool]

This is great help...thanks Jeff.

What if I wanted to include two numbers in the sales number field?

In the cases above, it includes 42. What if I wanted to include 42 AND 43?

Thanks again,

[jeffreybrown]

Two options...

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[nakedfool]

Thanks Jeff...man, you saved me hours, if not days, of work by helping me with this...

[jeffreybrown]

Happy to help...

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save