SUMIF formula returning a 0 value

[the machine]

Hello all, first time poster here.

I'm looking for some help with a sumif formula that is not working properly and I'm not sure what to do to remedy it.

I'm working on a spreadsheet that is a general ledger for accounting and it has numerous source codes, such as AR, AP, IN, NAR, etc. I need to write a sumif formula that will take each individual code and sum them up individually for a Macro for report reconciliations.

I have another spreadsheet that has categories broken down by expense and revenue and created a sum if formula there. It reads =SUMIF(J2:J999999,"Expense",L2:L999999) and it works fine. I double checked the values by filtering the column as well.

My current sumif reads =SUMIF(J2:J1000000,”AP”,O2:O1000000).

Both columns on each spreadsheet are set to general so it should be reading the text properly. I also checked the values by filtering the source to AP and it should not be 0.

Any thoughts?

Thanks in advance

[BadlySpelledBuoy]

Any chance we could see a sample of the data in a workbook?

BSB

[the machine]

Sorry, I'm not quite sure how to do that. Do you mean a screenshot?

[BadlySpelledBuoy]

Not a screenshot, a sample of the actual file.

Click on GO ADVANCED under where you type your message and then scroll down to Manage Attachments to open the upload window.
Quite straihtforward from there.

BSB

[BadlySpelledBuoy]

Can I just check something... The second formula in your original post is summing values in column O and the first in column L. Is that right?

BSB

[the machine]

I can't upload the file as it is confidential information.

What's odd is I copied part of the file to a new worksheet and typed in the same formula and it is returning a value.

I also have sumif formulas on the original worksheet with a different range and they work fine.

[MrShorty]

My first thought is that your numbers are "numbers stored as text". Setting the format to general does not prevent numbers from being stored as text and does not insure or cause them to be converted to real numbers. My first recommendation is to enter a formula like =ISTEXT(O2) and copy down. If all of them come back TRUE, then you need to do something to convert those numbers stored as text (which the SuMIF() function ignores) to real numbers.

[the machine]

Can I just check something... The second formula in your original post is summing values in column O and the first in column L. Is that right?

BSB

Yes, but those formulas are from two different worksheets.

The sumif formulas I used on this worksheet already read ledger accounts for values greater than 400000, then greater than 600000, then in between 400000 and 600000, and all three return the proper value from column O

=SUMIF(B2:B1000000,">400000",O2:O1000000)

=SUMIF(B2:B1000000,">600000",O2:O1000000)

=SUMIF(B2:B1000000,">400000",O2:O1000000)-SUMIF(B2:B1000000,">600000",O2:O1000000)

[BadlySpelledBuoy]

I agree with MrShorty and that's why a sample workbook would have been helpful.

If you have no left/right justification set on the cells and your numbers appear to the left of the cell then they're not stored as numbers. If they're to the right then they are.

There are easy ways to convert text numbers to real numbers, such as copy a blank cell, highlight the cells with the text numbers, right click and select paste special / add.
This will effectively add zero to each of the numbers and force them into real numbers.

There is also a trick with text to columns but I rarely use that.

BSB

[the machine]

My first thought is that your numbers are "numbers stored as text". Setting the format to general does not prevent numbers from being stored as text and does not insure or cause them to be converted to real numbers. My first recommendation is to enter a formula like =ISTEXT(O2) and copy down. If all of them come back TRUE, then you need to do something to convert those numbers stored as text (which the SuMIF() function ignores) to real numbers.

I already have formulas that use column O as the output and it works. It's something with the range here and I'm not sure what it could be.

[BadlySpelledBuoy]

Another thing it could be is the values in column J are not quite as your formula states.
For example, "AP " (with a trailing space) will be treated very differently by Excel than "AP" (without a trailing space) even though they will look the same on screen.

BSB

[the machine]

Another thing it could be is the values in column J are not quite as your formula states.
For example, "AP " (with a trailing space) will be treated very differently by Excel than "AP" (without a trailing space) even though they will look the same on screen.

BSB

Yes that was the first thing I checked. Unfortunately, not the issue.

It's odd that when I copy and paste a few lines to a new workbook and ran the same formula, it output a value.

[BadlySpelledBuoy]

Did you try the ISTEXT formula MrShorty mentioned?

Does the below formula work at all?

Please Login or Register  to view this content.

BSB

[the machine]

Did you try the ISTEXT formula MrShorty mentioned?

Does the below formula work at all?

Please Login or Register  to view this content.

BSB

output is #NAME?

[the machine]

Is it possible to have something wrong with the cell I was putting the initial formula in? I just created a new column and added random text, did another sumif and got no result. tried the original sumif in a different cell and got the correct output.

[BadlySpelledBuoy]

How is the cell that's not working formatted?

The SUMPRODUCT formula works for me. See attached.

BSB

[the machine]

It for sure has something to do with the way the cell is formatted. I'm not sure what format it is in. All my formula cells are set to Accounting. The only thing that changes when I switch between cells is the styles box. It changes from showing Top Row Accent2 Accent3... Bottom Row Comma Comma [0]... to Top Row Comma 2 Normal 2 Normal 3 Bottom Row Good Neutral Calculation.

This is for sure what the problem is. I will just disregard that cell.

Now the final problem with this formula is that I also need it to specifically output the AP transactions from column J, and only column B >600000. So I need this to have two conditions.

With multiple conditions does the sum range now come first? From what I have read on the web, it seems as this would be the case.

[MrShorty]

With multiple conditions does the sum range now come first? From what I have read on the web, it seems as this would be the case.

Assuming you are talking about changing from the SUMIF() function to the SUMIFS() function, then, yes, the order of the arguments is different for the SUMIFS() function: https://support.office.com/en-us/art...6-611cebce642b

[BadlySpelledBuoy]

You could copy the formatting from a cell where the formula does work and paste it into that cell/row. You can use the format painter tool to do that. It looks like a little paintbrush. Click on the cell that works, click the paintbrush icon and then click on the cell/cells you want to apply the formatting to.

As for the final problem, yes the sum range comes first.
=SUMIFS(RangeToSum,CriteriaRange1,Criteria1,CriteriaRange2,Criteria2)

BSB

[the machine]

Excellent thanks

However, I'm not quite sure how my formula should read.

=SUMIFS(O:O,B:B,>"600000",J:J,="AP")

Because that does not work. I'm sure I have something incorrect.

Thanks again for the help from you both. I added a bunch of rep to you

[the machine]

Just updated it to read

=SUMIFS(O:O,B:B,B:B>"600000",J:J,J:J="AP")

output is 0, should be 20MM

[MrShorty]

Examples of greater than/less than criteria that I have seen used put the greater than/less than symbol inside of the quotation marks rather than outside SUMIFS(O:O,B:B,">600000",...) Could that be causing the issue you are seeing?

[the machine]

Examples of greater than/less than criteria that I have seen used put the greater than/less than symbol inside of the quotation marks rather than outside SUMIFS(O:O,B:B,">600000",...) Could that be causing the issue you are seeing?

I have tried it both ways with both the = and the >. The only way that does not give me an error is the way I have the formula and it outputs 0. I'll give it a shot on a different worksheet as formatted and see if it will produce a value.

Edit, which is does not using this formula on a separate worksheet

=SUMIFS(S:S,J:J,J:J="Expense",B:B,B:B>"30000")

I've also tried it with and without quotes around the 30000

I just tried this as well from reading around the web

=SUMIFS(S:S,B:B,">"&"30000",J:J,"="&"Expense")

Output is still 0



Blah 4th edit.

I got it to work with my last formula. It was outputting 0 because the other spreadsheet I was working on I needed to do text to columns on column B.

All set.

Thanks again for all the help guys! I'm sure I'll be back soon

[BadlySpelledBuoy]

Try this:

Please Login or Register  to view this content.

BSB

[BadlySpelledBuoy]

Glad you got there in the end

Thanks for the rep point.

BSB