• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT

N

niting

New Member
Forum,


I am using the basic sumproduct formula with wild card search. I converted the numbers to text as per chandoo's previous post on sumif. However, I am getting 0 as an answer. Any ideas??. The formula I am using is


SUMPRODUCT(--($D$12:$D$344="2***"),--($I$12:$I$344="BP02"),--($J$12:$J$344))

where the entries in Col D has been converted to text using Text formula.


Thanks

Niting
 
Niting

Give the following a burl:

=SUMPRODUCT((LEFT($D$12:$D$344,1)="2")*($I$12:$I$344="BP02"),($J$12:$J$344))


The above will also work with column D as numbers
 
Thanks Hui,


It worked.


BTW was just wondering why some formulaes work sometime and y sometime not. Does it have to do with formatting of the numbers and text in the sheet???
 
The way you had "2***" said to look for some text which was 2**** which didn't exist

and so 0 was the correct answer

I don't believe you can just use wildcard like you tried


I've found that formulas always do what we tell them,

If we ask them to do something wrong they will,

its our logic thats wrong not there interpretation of it.
 
YES Master, Point taken. In the process of developing more clarity. So hopefully would be asking excel right questions to achieve right results. :)
 
Hui

"Give the following a burl:"

I very near besmirched me britches.

(Apologies for off topic.)
 
@All

Maybe I shouldn't use Australian Slang in responses to posts ...


Please refer to the Australian Slang section of

http://dictionary.babylon.com/burl/

Apologies to others who maybe mistook my meaning.
 
You must log in or register to reply here.
Back
Top