Using Sum(IF(AND(....))) Not working and can't figure out why

[DeeRok]

Sorta complicated so will do my best to explain...
Here is what my data looks like (did my best to format it but came out a little weird, hope it's still readable):

Please Login or Register  to view this content.

So I'm trying to write a formula where if in column B, the number after the / is greater than the number before, and the name in column A is Alex, it adds the values in column C. I wrote a formula that says TRUE when I place it in row 4, but my formula is still not working.

Here is the formula I am using:
=SUM(IF(AND(IFERROR(RIGHT(B1:B6,LEN(B1:B6)-SEARCH("/",B1:B6))/LEFT(B1:B6,SEARCH("/",B1:B6)-1),0)>=1,A1:A6="ALEX"),C1:C6))

I press ctrl-shift-enter and I come back with 0.

When I put in the formula:
=IF(AND(IFERROR(RIGHT(B4,LEN(B4)-SEARCH("/",B4))/LEFT(B4,SEARCH("/",B4)-1),0)>=1,A4="ALEX"),C4)
I get 20

I'm not sure what I'm doing wrong here

Thank you.

Just wanted to add: I got this formula to work using nested if statements, but would rather use the AND function if possible since there are many more criteria, I just included these to simplify it.

[Glenn Kennedy]

This doesn't look too complicated. BUT it is not clear what result you EXPECT to see in your examples. Please post a column D, showing what you EXPECT as the answer.

[DeeRok]

I don't want a column D. I just want one cell with a formula that would sum all the values of column C where the criteria are met... I can post a sample excel document if it's allowed (new to this forum, not sure the rules) and would be helpful.

Basically The only value that should be added is C4

[sktneer]

Try this......

Please Login or Register  to view this content.

[Glenn Kennedy]

Yes but added to WHAT? In the case of row 4, 20 would be added to??? 500 or 3000?

[DeeRok]

Try this......

Please Login or Register  to view this content.

I made code that works with nested if statements, but would prefer to use the AND function if possible but the actual spreadsheet has many more criteria

[DeeRok]

Yes but added to WHAT? In the case of row 4, 20 would be added to??? 500 or 3000?

Sorry if I wasn't clear, I just mean the values would be summed. Basically the formula sktneer gave, but don't want to use nested if's if possible.

There will be another cell, F50, that will contain a sum of all the cells of column C where the criteria is met.

[sktneer]

The problem with the AND inside the IF statement is, if you get any false value inside the array returned by the AND statement, the AND will evaluated to be False and hence you will not get the correct value. Instead use nested IF statement.

[DeeRok]

Got it sktneer, thanks a lot. Is there any better way to do it with 10+ criteria without it looking like a mess?

[sktneer]

You have no other option but to add the multiple criteria using the nested IF statements.

If that takes care of you question, please mark your thread as solved by selecting the Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[azumi]

If you need add more criteria use this:

=SUM((RIGHT(B1:B6,LEN(B1:B6)-SEARCH("/",B1:B6,1))/LEFT(B1:B6,SEARCH("/",B1:B6,1)-1)>=1)*(A1:A6=A9)*(C1:C6))

you can add more criteria with add bracket "*(ranges=criteria1)*(ranges=criteria2) and the last bracket is number you'll add up