Sumproduct + Isnumber(search) ?

ddasilva

New Member
Joined
Feb 27, 2019
Messages
8
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.
COIN0.71
CASH5.00
CREDITCARD478618.99
CASH4.00
CREDITCARD0887103.74

<tbody>
</tbody>

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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
can try the sumif() with wildcard like this


Book1
AB
1COIN0.71
2CASH5
3CREDITCARD478618.99
4CASH4
5CREDITCARD0887103.74
6
7
8CREDIT122.73
Sheet1
Cell Formulas
RangeFormula
B8=SUMIF(A1:A5,A8&"*",B1:B5)
 
Upvote 0
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?
 
Upvote 0
you can modify the formula like this


Book1
AB
1COIN0.71
2CASH5
3VISA478618.99
4CASH4
5AMEX0887103.74
6
7
8Visa122.73
9Amex
Sheet1
Cell Formulas
RangeFormula
B8=SUMIF($A$1:$A$5,A8&"*",$B$1:$B$5)+SUMIF($A$1:$A$5,A9&"*",$B$1:$B$5)
 
Upvote 0
=SUMPRODUCT(ISNUMBER(SEARCH("CREDITCARD",$A$1:$A$5))*($B$1:$B$5))
95aaf1c352c95c32c124eed239aaed5e-full.png
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top