Try to countifs and sumifs value with "-"

[molittlemo]

I have exported some value from MYOB, the accounting program, like this:

Column A Value Column A *1
6-1826 10 #VALUE!
6-1830 10 #VALUE! =COUNTIFS(A:A,">6-1825",A:A,"<6-1899") 3
6-1898 10 #VALUE! =COUNTIFS(A:A,">6-1900",A:A,"<6-1999") 0
6-1900 10 153
6-1910 10 3805 =SUMIFS(B:B,A:A,">6-1825",A:A,"<6-1899") 30
6-1932 10 11841 =SUMIFS(B:B,A:A,">6-1900",A:A,"<6-1999") 0
6-1951 10 18780


Column A is the account no. exported from MYOB.
Column B I assume each them is $10
Column C is column A*1
When I try to count or sum values between "6-1825" and "6-1900", it works perfect.
But when I try to count or sum values between "6-1900" and "6-1999", it doesn't work.

Can anyone please point out the problem, and how should I solve this?
Thanks a lot

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[molittlemo]

Try

Please Login or Register  to view this content.

Thanks a lot for the reply.
Sorry I did not make it clear.
My purpose it to sum up the value in column B given the range in column A

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

Change the criteria ( highlighted part) according your need.

[davsth]

it would be better posting a sample, if the above don't work, if I type 6-1899 in a cell thats what it stays as. however 6-1900 becomes june 1900. What actually are your values in column A. Hence a sample would be useful!

[molittlemo]

it would be better posting a sample, if the above don't work, if I type 6-1899 in a cell thats what it stays as. however 6-1900 becomes june 1900. What actually are your values in column A. Hence a sample would be useful!

Thanks and sorry I am new to here, not too sure how it works.
Please see the attchment

[molittlemo]

Thanks and please refer to the attachment.
It does not work for any right 4 digits lager than 1900.

[FDibbins]

......

Column A Value Column A *1
6-1826 10 #VALUE!
6-1830 10 #VALUE! =COUNTIFS(A:A,">6-1825",A:A,"<6-1899") 3

Any time you have any character other than just numbers in a cell, ,that cell's contents become text, so when you have something like 6-1825, that is text, even though it looks like a number. Because it is text, you cannot use > or < for any tests, thats liks asking excel if Tree is > whale.

Probably the best way around that would be to use a helper column, and maybe convert the - to a . so that 4-1100 becomes 4.11 and 4-1440 becomes 4.144, then you CAN do a test for > or <

[shukla.ankur281190]

Try with helper column

C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

Drag down and change the cell format in to General from Text.

Now use

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[FDibbins]

Try with helper column

C2

Please Login or Register  to view this content.

...

That wont work, OP beeds to be able to break the "4" range down into different levels
=SUMIFS(B:B,A:A,">4-1000",A:A,"<4-1500")
=SUMIFS(B:B,A:A,">4-1500",A:A,"<4-1900")
etc
Thats why I thought it would be better to just convert to 4.xxx

[shukla.ankur281190]

@Mr. Ford ! I did two helper columns for the working both criteria.

[molittlemo]

But its strange that it works between 4-1000 and 4-1500 but not working between 6-2000 and 6-2100..

[Jonmo1]

You're own guess in cell A19 of the document you posted is correct.
It is in fact interpreting digits 1900+ as dates.
6-1900 becomes June 1st 1900

You'll be best off separating the values with helper columns as suggested.

[molittlemo]

Thanks I will try

[molittlemo]

Thanks it works perfectly~~~~

[xlnitwit]

Perhaps simply
=SUMPRODUCT($B$2:$B$100,($A$2:$A$100>"4-1000")*($A$2:$A$100<"4-1500"))
without helper columns.

[molittlemo]

Thanks.
It seems work too~~

[molittlemo]

its interesting that "sumproduct" can identify the value larger than 6-1900 but "sumifs" can not..

[Jonmo1]

The difference is that Sumifs converts 6-1900 to a Date, but Sumproduct does not.

The xxIFs family of functions are designed to treat anything that looks like it might be a numeric value as a number.
Sumproduct itself isn't actually looking at the "4-1900" value.
Before sumproduct even looks at the values presented to it, they are processed by straight cell comparisons and math operations.

So you have this inside sumproduct
($A$2:$A$100<"6-1900")
That is first processed as an array of true/false statements.
Is A2 < "6-1900" <- This comparison does not convert that string to a date.
Is A3 < "6-1900"
Is A4 < "6-1900"
Etc.
You end up with an array of True's and False's
{TRUE,TRUE,FALSE etc..}

Then by the virtue of the multiplication action against that array, they are further converted into 1's and 0's (TRUE = 1, FALSE = 0)

Only then does sumproduct start to work with the values by multiplying that array of 1's and 0's with the array $B$2:$B$100\
$B$2:$B$100*{1,1,0,etc}

Then sums those results.


On the other hand, sumifs DOES look directly at the "6-1900" string while it is doing it's processing.
And it gets converted to a date.

Hope that helps.