Sunif to return text?

[treaves04]

Been working this issue for a while and haven't been able to solve it myself. I can two workbooks, Rent Rolls and Rent Rec. I have several Named columns on rent Rec to keep things simple. Here is the formula I use in Rent Rolls:

=SUMIFS(RentAmtRecd,RentFrom,$C4,RentDateRecd,">="&F$1,RentDateRecd,"<"&G$1)

This formula sums all the rent (partial or whole) between the date ranges F1 and G1 (which scroll across the page).

The Problem I have is I need to be able to enter text on 'Rent Rec' and it show in the cell, but SUMIFS ignores all non-numeric Values. How can I get text such as "Evicted" or "Remodeling" into the 'Rent Rolls' cell where this formula is running?

 

[kweaver]

If the text condition is met, will that person be in the RentFrom only 1 time?
If so, I think there is a rather simple (and ugly) solution I found that works. Something like the following, but I don't know what your range is for the "table" so I used A2:D1000.

IF(ISERROR(SUMIFS(RentAmtRecd,RentFrom,$C4,RentDateRecd,">="&F$1,RentDateRecd,"<"&G$1),VLOOKUP($C$4,A2:D100,4,false),SUMIFS(RentAmtRecd,RentFrom,$C4,RentDateRecd,">="&F$1,RentDateRecd,"<"&G$1))

 

[treaves04]

If the text condition is met, will that person be in the RentFrom only 1 time?
If so, I think there is a rather simple (and ugly) solution I found that works. Something like the following, but I don't know what your range is for the "table" so I used A2:D1000.

IF(ISERROR(SUMIFS(RentAmtRecd,RentFrom,$C4,RentDateRecd,">="&F$1,RentDateRecd,"<"&G$1),VLOOKUP($C$4,A2:D100,4,false),SUMIFS(RentAmtRecd,RentFrom,$C4,RentDateRecd,">="&F$1,RentDateRecd,"<"&G$1))

Unfortunately, the property (c4) will appear numerous times. Only once In the given date range. For Example:

January - $800 (Just the rent collected, formula works fine here)
February - Tenant Moves out ( would need to label "Move Out")
March - Work Done (Label as Remodel or whatever)
April - $900 (Formula works again.)

Keep in Mind I am tracking 90 Properties with a minimum of 12 transaction per year, so every property will show up at least 12 times a year, thus the reason for the date ranges when looking it up.

 

[kweaver]

It's not very easy to replicate a sample of your data. Could you provide one including a case or two that should be tested and what you want returned?