Finding a specific word in a cell and executing an array

**jgray152** · 01-09-2010, 05:30 PM

I hope the title explains ok.

I am playing with my sheet some more and basically I have a "list" of items to choose from in H5:H34.

List Contains below:
Full - CASH
Full - CHECK
Full - VISA
Full - MC.
Full - Disc.
Full - Amex
Balance - CASH
Balance - CHECK
Balance - VISA
Balance - MC
Balance - DISC.
Balance - AMEX
Deposit - CASH
Deposit - CHECK
Deposit - VISA
Deposit - MC
Deposit - DISC.
Deposit - AMEX
Refund
Quote

I want an array formula that I can use that will allow excel to search for the word "cash" (Or VISA,CHECK,MC, etc..) in column H5:H34 which will have other words such as (Balance, Deposit, Full, etc. ) in the same cell and then add up some dollar amounts in column J5:J34.

My code right now that I was trying which has failed. (This code is in the Cash cell which adds up all dollar amounts put in with cash)

=SUM(IF(H5:H34="",,(IF(H5:H34="quote",,IF(H5:H34="refund - cash",IF(MATCH(H5:H34="cash",-J5:J34)),IF(MATCH(H5:H34="cash",J5:J34)))))))

This portion is what I really need
IF(MATCH(H5:H34="cash",-J5:J34)),IF(MATCH(H5:H34="cash",J5:J34))

MATCH I believe is the wrong thing to use as I read it only gives you a "position" of where something is.

**JBeaucaire** · 01-09-2010, 05:36 PM

The most basic part of your question is SUMIF():

=SUMIF(H5:H34, "*CASH*", J5:J34)

You can string other SUMIF()s together to subtract other stuff, or you can try to construct a SUMPRODUCT() to do it:

=SUMPRODUCT(--(H5:H34="*cash*"), --(H5:H34<>"refund - cash"), J5:J34)

**jgray152** · 01-09-2010, 05:52 PM

Gosh.... thats it? haha

What does it mean when you put in "<>" in the following ?
--(H5:H34<>"

**JBeaucaire** · 01-10-2010, 07:49 PM

Originally Posted by jgray152

Gosh.... thats it? haha

What does it mean when you put in "<>" in the following ?
--(H5:H34<>"

<> in an Excel formula means "greater than or less than" or "is any value other than..."

=A1<>"dog" means "this is TRUE if cell A1 does NOT have the value 'dog' "

**jgray152** · 01-09-2010, 06:11 PM

So I did the following

=SUM(IF(H5:H34="",,(IF(H5:H34="quote",,IF(H5:H34="refund - cash",SUMIF(H5:H34, "*CASH",J5:J34),SUMIF(H5:H34, "*CASH", J5:J34))))))

For some reason if there is ANY selection made in more than one cell in the H5:H34 column it will multiply the summed number by however many cells are filled in in Column H5:H34

so in H5:H34, if H5 says "FULL - CASH" and J5 says "$10.00" and H6 says "FULL - VISA" and J6 says "$300.00" basically the CASH summed cell will say $20.00 and the VISA summed cell will say $600.

If I only have H5 and J5 entered, than it will return the value $10.00 in the CASH summed cell which is correct.

**JBeaucaire** · 01-10-2010, 03:00 AM

I don't know what you're trying to do with that long version of your formula.

Click GO ADVANCED and use the paperclip icon to post up a sample of your actual workbook. Make sure the sample data is clear and manually mockup the answers you're looking for (no formulas there, please, just the answer and explanatory logic if it's not obvious.)