I'm looking for a formula to return text value if..... Similar to sumif formula but for text

[wz]

I want to know if there is a formula that is similar to SUMIF but for text. I want to return text values....

If last year & this year crop is corn (C) then return the field name(s)

example....
year year
2010 2011
field crop crop acres
1 C C 10
2 C B 15
3 C C 8

corn to corn fields - return value = "1, 3"
my sumif totals acres if corn to corn (18), now I am looking for field names ("1, 3")

Thanks

 

[SkipVought]

Hi,

Surely you data is more complex than this example.

The short answer is that there is no FORMULA that does that. Excel is not designed to analyze non-tabular data.

Forum707

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

With a little imagination, you can do what you want with pivot-tables. If you haven't used pivot tables before, now is the time for some Googling. You won't regret it.

I'm assuming that you have your data in four separate columns, field-No, crop-in-2010, crop-in-2011, field-size. If you create a pivot table using all of these, it can summarise all combinations of crops in the two years (use these as the left-hand fields in your pivot table), and either count how many times the combination happens, or give a sum of field-area for each combination.

If you double-click on the row in the pivot table holding the correct combination, it will create a new worksheet listing all individual fields that have that combination of crops.

(incidentally, I'd suggest keeping the crop for each year in a separate column, even though you could do the same thing by concatenating them in a single column and looking for "CC"; it makes it easier to update the workbook each year.)

 

[judgeh59]

if Lionelhill is correct about how you have you data set up you can use VBA to loop through the rows and collect them in an array and then do whatever you want to with said array...if you go that route then it would make sense to repost this in the forum that Skip suggests...

Ernest

Be Alert, America needs more lerts

 

[xlhelp]

This can be done manually without using VBA; however you only get the result without the formula.

Assuming your data is in rows 2, 3 and 4 (a2:d4)

Type cc in cell E1 (that's the result you expect; corn for both years)
In cell e2 type the formula: =IF(B2&C2=$E$1,A2&", ","")
Copy the formula down to e3 and e4

Make cell f2 active.
In the formula bar type e2:e4
Then type an equal sign in front of e2 and type the word TRANSPOSE.... that is =TRANSPOSE(e2:e4)
without moving away from the formula bar, hit F9 on your keyboard.
Remove the curly brackets
type the word CONCATENATE right after the equal sign; open bracket; go to the end of the string; close bracket.
Hit enter



Avoid Hangovers. Stay drunk.

 

[wz]

Thanks to everyone who responded! I do work for 100+ farmers and found that the way I am doing it in Excel seems to be the best. I use a sumif for acre totals and then use nested if functions to return the fields name desired.

I was simply trying to see if there is a forumula that I didn't know about (a sumif for text).

I have many, many field crop variations. I evaluate them by prior years crops to current year crops and I simply have a longer spreadsheet if farmers have many fields.

Thanks again!
WZ