Two criterias: Sum all rows where: Row contains string + column A contains specific word

1. Two criterias: Sum all rows where: Row contains string + column A contains specific word

Range B4:E100, want to sum all results of the followng

* Looking for the string "in" each row of the range, for example "inside". If "in" occurs more than one time in the same row, count only as one.
* Also, column A in the same row as the string "in" needs to be equal "CIA".

So, if column A row 5 =CIA, and row 5 contains the string "in", count as one. Sum all the number of rows that matches these two criterias.  Register To Reply

2. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Hi jokris,

Here is the formula I would use to determine if the string "in" occurs in any cells between Columns B-E. The formula below would be inserted in Range A4 and then copied down to cell A100 so each row is checked. To sum up how many rows have the string "in" in them, paste the following formula in a cell outside of the range "A4:E100" to display the result. When put insert it, make sure you then set your cursor at the end of the formula and then hit "[CNTL] [SHIFT] [ENTER]" so excel recognizes it as an array formula, otherwise it won't calculate. Hope that helps,

Dan  Register To Reply

3. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Try this...

Data Range
 A B C D E F G H I 4 KIA data inner data data CIA In 2 5 CIA data data inside ink ----- ----- ----- ----- 6 ACI data data data data 7 DEF dink data data data 8 CIA data data data data 9 CIA data data data data 10 CIA pink link link drink

This formula entered in I4:

=SUMPRODUCT(--(A4:A10=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E10)),{1;1;1;1})>0))  Register To Reply

4. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

You could use this formula:

=SUMPRODUCT((A4:A100="CIA")*(ISNUMBER(SEARCH("in",B4:B100&C4:C100&D4:D100&E4:E100))))

Hope this helps.

Pete  Register To Reply

5. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Range concatenation is inefficient.  Register To Reply

6. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo Is it possible to use =INDIRECT("A"&ROW()) to refer to a value instead of =G4?

Trying but getting Value error.  Register To Reply

7. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo Originally Posted by Tony Valko Range concatenation is inefficient.
Well, it's only 4 cells, and this seems like a one-off calculation. Is doing ISNUMBER(SEARCH 4 times per row that much better?

Pete  Register To Reply

8. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo Originally Posted by Pete_UK Well, it's only 4 cells
It's =ROWS(B4:B100)*4.

Probably won't notice any difference but I like to let folks know about these things.

In terms of efficiency...

Best: array multiplication - (Range1="this")*(Range2="that")
Better: nested IFs - IF(Range1="this",IF(Range2="that"
Only if you have to: range concatenation - Range1&Range2="thisthat"

Of course, if your file is only 10kbs and only has a few formulas you won't know the difference!   Register To Reply

9. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo Originally Posted by Pete_UK Is doing ISNUMBER(SEARCH 4 times per row that much better?
I tested the calculation times of these 2 formulas:

Formula1:

=SUMPRODUCT(--(A4:A100=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E100)),{1;1;1;1})>0))

Formula2:

=SUMPRODUCT((A4:A100="CIA")*(ISNUMBER(SEARCH("in",B4:B100&C4:C100&D4:D100&E4:E100))))

Did 5 calculations each and got the average of those 5 calculations:

Formula1: 0.000350
Formula2: 0.000636

Tested in Excel 2002 using Charles Williams RangeTimer method:

http://msdn.microsoft.com/en-us/library/aa730921.aspx  Register To Reply

10. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo Originally Posted by jokris =SUMPRODUCT(--(A4:A10=G4),--(MMULT(--ISNUMBER(SEARCH(H4,B4:E10)),{1;1;1;1})>0))

Is it possible to use =INDIRECT("A"&ROW()) to refer to a value instead of =G4?

Trying but getting Value error.
It would refer to Ax where x is the row number that the formula is entered on.

So, if the formula was entered in cell I4 then:

INDIRECT("A"&ROW()) evaluates to A4.  Register To Reply

11. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Yes, but it doesn't seem possible to replace G4 with INDIRECT("A"&ROW())?  Register To Reply

12. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Try it like this...

=SUMPRODUCT(--(A4:A10=T(INDIRECT("A"&ROW())))...

Assuming that whatever is in Ax is a TEXT entry.  Register To Reply

13. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Doesn't seem to work.

Perhaps not possible to achieve in Excel.  Register To Reply

14. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Works for me.

Here's a sample file that demonstrates this.  Register To Reply

15. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Thank you so much, think I got it now!  Register To Reply

16. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Double post  Register To Reply

17. Re: Two criterias: Sum all rows where: Row contains string + column A contains specific wo

Good deal. Thanks for the feedback!   Register To Reply