Sumproduct + Isnumber(search) ?

[ddasilva]

Hi All,

I have problem that I think I am overcomplicating and was hoping I could get some advice please.

In my spreadsheet there are two pertinent columns. One is a dollar amount and the other is a description.


ex.
[TABLE="width: 300"]
<tbody>[TR]
[TD]COIN[/TD]
[TD]0.71[/TD]
[/TR]
[TR]
[TD]CASH[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]CREDITCARD4786[/TD]
[TD]18.99[/TD]
[/TR]
[TR]
[TD]CASH[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]CREDITCARD0887[/TD]
[TD]103.74[/TD]
[/TR]
</tbody>[/TABLE]

How can I get a total of all the cells that have a certain phrase in their description? I want to know how much came in from all credit card deposits but the last 4 digits are never the same. I can do an isnumber + search to indicate which cells match but then I have to use sumif and a grand total. I also did something similar with index + match.

Is there a cleaner way? I'm guessing it might be sumproduct but I can't figure out the syntax at all.

Thanks in advance!

 

[AlanY]

can try the sumif() with wildcard like this

Book1
	A	B
1	COIN	0.71
2	CASH	5
3	CREDITCARD4786	18.99
4	CASH	4
5	CREDITCARD0887	103.74
6
7
8	CREDIT	122.73
Sheet1

Cell Formulas
Range	Formula
B8	=SUMIF(A1:A5,A8&"*",B1:B5)

 

[ddasilva]

Thank you for your suggestion but I neglected to look at my original data and was using the one from my workaround instead. I had used isnumber + search, if, and then right(4) to make that new list.

In place of "CreditCard" it will normally say VISA or AMEX to begin with. How could I shorten the process to accomplish the same thing?

 

[AlanY]

you can modify the formula like this

Book1
	A	B
1	COIN	0.71
2	CASH	5
3	VISA4786	18.99
4	CASH	4
5	AMEX0887	103.74
6
7
8	Visa	122.73
9	Amex
Sheet1

Cell Formulas
Range	Formula
B8	=SUMIF($A$1:$A$5,A8&"*",$B$1:$B$5)+SUMIF($A$1:$A$5,A9&"*",$B$1:$B$5)

 

[aqeelnokia99]

=SUMPRODUCT(ISNUMBER(SEARCH("CREDITCARD",$A$1:$A$5))*($B$1:$B$5))