Summing cells containing a text string within an array foruma

**absintheminded** · 09-08-2010, 01:16 PM

Hey all

I have set up a formula which checks for a number of conditions, and where those conditions are true the formula returns the sum value. For example:

{=SUM(('Bill Chronology'!$E$3:$E$50504<=$C5)*('Bill Chronology'!$E$3:$E$50504>$C4)*('Bill Chronology'!$G$3:$G$50504=$A$3)*('Bill Chronology'!$J$3:$J$50504))}

This formula works perfectly where the text in Cell $A$3 is exactly equal to the text in the array.

However, I now want to amend the formula, to return the sum value where the text in Cell $A$3 is not exactly equal to the text in the array.

For example I want the formula to report that the condition is met when:

$A$3 = "Cheese"; and
$G$3:$G$5054 = "Cheese is good"

Hope that you can help!

Many thanks

Al

**absintheminded** · 09-08-2010, 04:43 PM

Hopefully this will explain the problem a bit better.

Sheet "Bill Chronology" contains the following info:

Column E Column F Column J
date Cheese is good Value
date I like Cheese Value
date Where did the Cheese go? Value
date Bananas rock Value
date Onions smell Value

In the summary sheet cell A3 contains the text "Cheese"

I want the formula to count all the cells in Column F which contain the word "Cheese" (the word in A3) and to sum the value of all the cells in the corresponding column J.

The other conditions I have added to the formula in the first post are checks to see whether or not the dates of the entry fall into the relevant month and that bit works fine.

Hope you can help!

Thanks

Al

**NBVC** · 09-08-2010, 04:56 PM

If you can you should reduce the number of rows in the formula... it will be slow...

Please Login or Register  to view this content.

**DonkeyOte** · 09-08-2010, 04:58 PM

edit: too slow - but left as a slight variant (double unary) and also a little info. re: possibility of false positives

They are some BIG ranges you're using in your formula ... it will be pretty resource intensive to use an Array/SUMPRODUCT as you are.

Please Login or Register  to view this content.

Using SEARCH in the above context you're open to false positives (ie should Cheese appear embedded in another term - eg "DO is Cheesed Off")
If that's a concern and you have a consistent delimiter between words (space in your examples) you can modify the above accordingly to remove this risk... if the delimiters are (as I suspect) not that consistent you can't.

To reiterate though - the above is processing around 200,000 cells - that's a lot for an Array/SUMPRODUCT to work with.

**absintheminded** · 09-08-2010, 05:10 PM

Perfect - Cheers, the formula seems to work just as a SUM too.

It is a bit slow, but unfortunately I just have to put up with that.

Thanks

**DonkeyOte** · 09-08-2010, 06:00 PM

The SUMPRODUCT is in reality doing the same thing as the SUM Array - the only difference is that the former does not require Array entry making it a little more robust from an end users perspective.

(Arrays are generally better at handling underlying error values - given these are not seemingly an issue here I would use SUMPRODUCT myself)

If you really do have 50000+ rows of data to analyse then yes performance will be pretty hideous I'm afraid.

Without knowing how many more of these formulae you're running (and where) and also the nature of the values in C4/C5 (ie monthly, annual analysis etc) it's hard to know as to whether or not a concatenation & SUMIF based approach would be viable ... if it is it would be a lot of quicker.