Sumif total using wild card

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
I have drop-down account numbers is H516, formula in J516 works fine, but I want to add total in the list of account numbers, in cell H516, to get total off all the accounts.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
=SUMIF(B460:B513,"*"&H516&"*",L460:L513) <o:p></o:p>
<o:p> </o:p>
How can I do this.<o:p></o:p>
<o:p> </o:p>
Thanks.<o:p></o:p>
<o:p> </o:p>
Sohail <o:p></o:p>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
=SUMPRODUCT(--(ISNUMBER(MATCH(rng,B460:B513,0))),L460:L513)

where rng is the range of values being used in the dropdown in H516
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
Maybe try:

=sumproduct(--(isnumber(find(h516,b460:b513))),l460:l513)

Hope that helps.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
Thank you to both of you, although both formula are almost identical, I used schielrn formula, because xld's Match, formula can not work on my data, since in column B, I have numbers like 18600-2456, 18700-1234, etc.
But again, my original formula does the same, I need a formula that can do sum of all the accounts. what do I have to do to get it.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,626
Messages
5,469,785
Members
406,670
Latest member
Jimborusk13

This Week's Hot Topics

Top