# 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. ``Please Login or Register  to view this content.``
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. ``Please Login or Register  to view this content.``
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 ``Please Login or Register  to view this content.``
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