sum product & wild card

[subzerouk]

Hi all,

You were were very useful and fast to respond last time thanks again; hopefully you can help again...

=SUMPRODUCT((Lowers!$Q$1:$Q$1000='UPH DATA'!H3)*(Lowers!$A$1:$A$1000="9*"),Lowers!$J$1:$J$1000)


Above is a formula I'm trying to use. What it is intended to do is check one column in a sheet against a cell in another but also to check a column that contains barcodes against ones that only begin with the number 9.


However, it is just returning the answer 0


Any help appreciated


Thanks

 

[Andrew Poulsom]

Try:

=SUMPRODUCT((Lowers!$Q$1:$Q$1000='UPH DATA'!H3)*(LEFT(Lowers!$A$1:$A$1000,1)="9"),Lowers!$J$1:$J$1000)

 

[subzerouk]

Ok thanks will give it a go later and let you know of the results

 

[subzerouk]

Worked a treat thanks

Just another quick question if I may...
On the same workbook I'm trying to do a countif of how many times it occurs. On a previous workbook for a multiple criteria count I used:

=SUMPRODUCT(($N$1:$N$1000=$P$43)*($M$1:$M$1000=P45))
Which works fine. However, I tried what I thought would be a similar version but I just get a value error:

=SUMPRODUCT((Lowers!$Q$1:$Q$1000='BOOKS DATA'!A3)*(LEFT(Lowers!$A$1:$A$1000="9")))


Any thoughts?


Thanks again

 

[Aladin Akyurek]

Worked a treat thanks

Just another quick question if I may...
On the same workbook I'm trying to do a countif of how many times it occurs. On a previous workbook for a multiple criteria count I used:

=SUMPRODUCT(($N$1:$N$1000=$P$43)*($M$1:$M$1000=P45))
Which works fine. However, I tried what I thought would be a similar version but I just get a value error:

=SUMPRODUCT((Lowers!$Q$1:$Q$1000='BOOKS DATA'!A3)*(LEFT(Lowers!$A$1:$A$1000="9")))


Any thoughts?


Thanks again

It should be:

=SUMPRODUCT((Lowers!$Q$1:$Q$1000='BOOKS DATA'!A3)*(LEFT(Lowers!$A$1:$A$1000)="9"))

Also...

=SUMPRODUCT(--(Lowers!$Q$1:$Q$1000='BOOKS DATA'!A3),--(LEFT(Lowers!$A$1:$A$1000)="9"))

 

[subzerouk]

Ah ok thanks I'll give it a go

 

[subzerouk]

Worked greated thanks again!

 

[Aladin Akyurek]

Worked greated thanks again!

You are welcome. Thanks for providing feedback.