Can this SUMPRODUCT formula contain 000's

[Mister H]

Hi:

I am trying to alter a formula and it is not working for me do to the fact that the numbers I am trying to sum begin with 0 (zero).

This is what I need:

=IF(SUMPRODUCT(--(DebitTrans=0055327),DebitMonies)>0,SUMPRODUCT(--(DebitTrans=0000001),DebitMonies),"")

When I paste this into Excel it changes to:

=IF(SUMPRODUCT(--(DebitTrans=55327),DebitMonies)>0,SUMPRODUCT(--(DebitTrans=1),DebitMonies),"")

Can the formula be altered to work or should I try and coeme up with a workaround?

THANKS,
Mark

 

[Jonmo1]

You could do this

=IF(SUMPRODUCT(--(DebitTrans+0=55327),DebitMonies)>0,SUMPRODUCT(--(DebitTrans+0=1),DebitMonies),"")

 

[JamesW]

Hi,

How did you get the 0's there? A custom format or using ' ('0055327)?

If it's a custom format then you need to use the number (55327), if it's a ' (which is text) then you need to put speech marks around it in the formula ("0055237").

 

[Jonmo1]

Or

=IF(SUMIF(DebitTrans,55327,DebitMonies)>0,SUMIF(DebitTrans,1,DebitMonies),"")

 

[Mister H]

THANKS jonmo1 and James

I will give that your suggestions a try. For the record the number I am summing is acquired through this formula:

=IF(B120="","",LOOKUP(B120,DebitMerchantNo,DebitDescNo))

The DebitDescNo table is text.

Again THANKS for your assistance. I think I will change to the SUMIF instead of the SUMPRODUCT. Not really sure what the difference is but I like the looks of the SUMIF better

Take Care,
Mark

I just tried:

=IF(SUMIF(DebitTrans,55327,DebitMonies)>0,SUMIF(DebitTrans,1,DebitMonies),"")

and I did not get an error but it did not sum up the monies. I may have done something wrong. I will check my named ranges <!-- / message --><!-- sig -->

 

[Jonmo1]

Glad to help, thanks for the feedback..

I'm not sure of the why either....
But Sumproduct doesn't treat "numbers stored as text" as real numbers
But Sumif Does

edit...

Seems only the CRITERIA part of sumif works with "numbers stored as text"
But the SUM part of sumif does not...

 

[Mister H]

THANKS for the info jonmo1.

In looking at the spreadsheet again I may be able to eliminate one critieria and just SUMIF when it equals 0055327.

I play around with the SUMIF formula and this will work for me:

=SUMIF(DebitTrans,55327,DebitMonies)

THANKS Again
Have a GREAT day,
Mark