Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Need a sum if that compares two columns for the criteria, then sums a third column. The issue is, I want to compare the two columns using two different criteria -- not sure if excel supports And statements when working with arrays.
Solved by K. J. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 11/02/2018 - 02:46
Hello!
Excelchat Expert 11/02/2018 - 02:46
Welcome to Excel chat.
User 11/02/2018 - 02:46
Hey i can't send the file over because its a work file
User 11/02/2018 - 02:46
but i can explain it very clearly
Excelchat Expert 11/02/2018 - 02:47
Yeah sure. We can work with dummy data in online sheet.
Excelchat Expert 11/02/2018 - 02:47
User 11/02/2018 - 02:47
=SUM(IF(AB21:AB122>AA21:AA122,\$D\$21:\$D\$122))
User 11/02/2018 - 02:47
this is my formula right now
Excelchat Expert 11/02/2018 - 02:47
Ok.
User 11/02/2018 - 02:47
it checks if column BB is greater than AA
User 11/02/2018 - 02:47
if it is, it sums D
User 11/02/2018 - 02:48
however, i also need to check if column AA is equal to zero
User 11/02/2018 - 02:48
=SUM(IF(AND(AA21:AA122=0,AB21:AB122>AA21:AA122),\$D\$21:\$D\$122))
Excelchat Expert 11/02/2018 - 02:48
Ok.
User 11/02/2018 - 02:48
this is what i came up with, but it doesn't work
Excelchat Expert 11/02/2018 - 02:48
Ok.
User 11/02/2018 - 02:48
I don't think excel supports and statements when working with arrays
User 11/02/2018 - 02:48
but I'm not sure... maybe there's another way
Excelchat Expert 11/02/2018 - 02:49
Let's try to put some data, and then we can work out our way.
User 11/02/2018 - 02:49
to the right, my screen says "the connectio nwas reset"
User 11/02/2018 - 02:49
under the document preview
Excelchat Expert 11/02/2018 - 02:50
You can refresh the page, and everything should be back.
User 11/02/2018 - 02:50
no luck. compnay might be blocking it. Ill Connect from my phone
User 11/02/2018 - 02:50
be right back
Excelchat Expert 11/02/2018 - 02:50
No probs, you can't do that.
User 11/02/2018 - 02:50
like hotspot
User 11/02/2018 - 02:50
to my phone
Excelchat Expert 11/02/2018 - 02:50
Ok. You can give it a try. But let's be here only.
User 11/02/2018 - 02:50
yeah, one sec, i'll be right back
Excelchat Expert 11/02/2018 - 02:50
Ok.
User 11/02/2018 - 02:51
I'm back
Excelchat Expert 11/02/2018 - 02:52
Great.
Excelchat Expert 11/02/2018 - 02:52
I can see some data there.
User 11/02/2018 - 02:52
can we work with arrays on here?
User 11/02/2018 - 02:52
i can't hit "Shift +Enter"
Excelchat Expert 11/02/2018 - 02:52
Yes. You should be able to do that.
User 11/02/2018 - 02:53
that formula isn't working because its an array :/
User 11/02/2018 - 02:54
hmm.. sorry not sure what's wrong
Excelchat Expert 11/02/2018 - 02:54
Wait.
User 11/02/2018 - 02:54
kk sorry
Excelchat Expert 11/02/2018 - 02:54
As I see, you are trying to compare multiple cells to one.
User 11/02/2018 - 02:55
yeah, that formula works in my worksheet
User 11/02/2018 - 02:55
its not working here unfortunately
Excelchat Expert 11/02/2018 - 02:55
It's ok.
User 11/02/2018 - 02:55
i don't think google sheets supports that functionality
Excelchat Expert 11/02/2018 - 02:55
No worries, I can work in offline sheet and help with you that.
User 11/02/2018 - 02:55
that would be awesome
Excelchat Expert 11/02/2018 - 02:55
User 11/02/2018 - 02:55
ok great
User 11/02/2018 - 02:55
so that formula should work
Excelchat Expert 11/02/2018 - 02:56
Ok. Let me see to that. Please wait for few moments.
User 11/02/2018 - 02:56
sure
Excelchat Expert 11/02/2018 - 02:59
Yes your formula in F9 is working.
Excelchat Expert 11/02/2018 - 02:59
Now you need second condition as well.
Excelchat Expert 11/02/2018 - 02:59
What's that condition?
Excelchat Expert 11/02/2018 - 03:01
hello?
Excelchat Expert 11/02/2018 - 03:02
I am waiting for your response.
Excelchat Expert 11/02/2018 - 03:03
There?
Excelchat Expert 11/02/2018 - 03:11
This formula is working in offline copy.
Excelchat Expert 11/02/2018 - 03:11
=SUM(IF(AND(F8:F12=0,G8:G12>F8:F12),D8:D12))
Excelchat Expert 11/02/2018 - 03:12

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.