• 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 - Can you use wild cards?

S

Sparkgirl

New Member
Follows on from previous post:-


I'm from an Access background and trying to make Excel do some of the functions I would normally build in Access.


For example in my spreadsheet

=Sumproduct(--(k2:k1001="P10001"),--f2:F1001) - calculates the cost of all the materials used against job P10001


However I have completed more than one job and would like to calculate all the materials used in the current financial year! (luckily all the job start P100) is it possible to build a formulae?


Have tried

=Sumproduct(--(k2:k1001="P100*"),--f2:F1001)


And

=Sumproduct(--(k2:k1001="*P100*"),--f2:F1001)


Both return the same answer, which is the total value of all of the stock and not just the stock used in "P100" projects.


Many thanks

Sparkgirl
 
Hi ,


Assuming you only want to check for jobs starting with "P100" , use the following formula :


=SUMPRODUCT(--(left(k2:k1001,4)="P100"),--(f2:F1001))


Narayan
 
You must log in or register to reply here.
Back
Top