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!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,146
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
can try the sumif() with wildcard like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">COIN</td><td style="text-align: right;;">0.71</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">CASH</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">CREDITCARD4786</td><td style="text-align: right;;">18.99</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">CASH</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">CREDITCARD0887</td><td style="text-align: right;;">103.74</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">CREDIT</td><td style="text-align: right;background-color: #E2EFDA;;">122.73</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=SUMIF(<font color="Blue">A1:A5,A8&"*",B1:B5</font>)</td></tr></tbody></table></td></tr></table><br />
 

ddasilva

New Member
Joined
Feb 27, 2019
Messages
8
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,146
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you can modify the formula like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">COIN</td><td style="text-align: right;;">0.71</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">CASH</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">VISA4786</td><td style="text-align: right;;">18.99</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">CASH</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">AMEX0887</td><td style="text-align: right;;">103.74</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Visa</td><td style="text-align: right;background-color: #E2EFDA;;">122.73</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Amex</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$5,A8&"*",$B$1:$B$5</font>)+SUMIF(<font color="Blue">$A$1:$A$5,A9&"*",$B$1:$B$5</font>)</td></tr></tbody></table></td></tr></table><br />
 

aqeelnokia99

Board Regular
Joined
Dec 23, 2018
Messages
65
=SUMPRODUCT(ISNUMBER(SEARCH("CREDITCARD",$A$1:$A$5))*($B$1:$B$5))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,510
Messages
5,523,318
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top