Can you use text in a =+SUMIF formula

[kellitony]

I have a roadblock that i cannot get around.


I deal in Realestate and use a Excel worksheet to track commissions for my sales people. What I need to have happen is:


Have closing buyers last name (Example- Smith)populate when the date in a cell is found in a range. So I need it to pull all of the buyers name that closed on that date.


I have it doing the samething using =+SUMIF and it pulls all of the commissions for the dates, but cannot get it do it with text in lieu of a number.


Example:


If the row reads(In seperate cells): Smith...$2800....11/15/12

Jones...$3500....12/30/12

Bates...$2600....11/15/12


I currently have it pulling and totaling the commission amount for the pay dates (Example $5400....11/15/12 and $3500.....12/30/12) So how can do the same with text so I can also add the names associated with the commission total. So it would look something like this: 11/15/12...$5400....Smith....Bates.


Hope this makes sense.

 

[3G]

To use text in a SUMIF you have to do is put the name (or text)in quotes =SUMIF(Name,"Bob",Commissions)*. Be sure that the cell with the name is formatted for text so it can read it. Also, you might need to TRIM the cells to make sure you don't miss any (i.e. Bob has a space after his name). Also, the text in the formula has to exactly match the text you are looking for.


*I'm using fake named ranges here...

 

[Luke M]

So, on 1 row, you want to see the date, the total sales, and each sales person name associated with that?


Assuming your original data is in columns A:C, and the summary data date starts in E2

=IF(COUNTIF($C:$C,$E2)<COLUMNS($E$2:E$2),"",INDEX($A:$A,SMALL(IF($C$2:$C$100=$E2,ROW($C$2:$C$100)),COLUMN(A$1))))


This is an array formula that needs to be confirmed using Ctrl+Shift+Enter. Copy formula to the right as far as you think would ever be needed. Sections in bold are used as "counters" and should not need to be modified.