• 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.

HELP with SUMIFS

P

phillygirl777

Member
Hello,

I know that there is an error and it's no the right way to do it, but how do I incorporate the text value function into my SUMIFS formula? I need to get a sum of the numbers if the first 2 characters of a cost center are numeric vs letters. Here is what I got so far (I know it's not right - need help with the right-most function):

=SUMIFS('Data Tab'!C:C,'Data Tab'!A:A,$A2,'Data Tab'!B:B,ISNUMBER(VALUE(LEFT('Data Tab'!B:B,2))))

Please help with the red part of the formula. I am getting a #SPILL! error.

Thank you!
 
If you have 365:
=SUM(FILTER('Data Tab'!C.:.C,('Data Tab'!A.:.A=$A2)*(ISNUMBER(-(LEFT('Data Tab'!B.:.B,2))))))

SUMIFS is not really going to work for what you are doing.
 
Debaser said:
A2 is blank in that file, so there are no matching results. You should have used A1 as the criterion cell.

To handle no matches:
=SUM(FILTER('Data Tab'!C.:.C,('Data Tab'!A.:.A=$A2)*(ISNUMBER(-LEFT('Data Tab'!B.:.B,2))),0))
Click to expand...
Thank you, but it still didn't work - the error now is #VALUE.

I haven't used the FILTER function in a while, but why did you put the minus sign before LEFT?

Thanks again!
 
LEFT always returns text, so applying the unary - operator will either convert it to a number or return an error and pass the result to ISNUMBER to return TRUE or FALSE accordingly.

The formula works in your sample file. Are there any errors in your real data file?
 
You must log in or register to reply here.
Back
Top