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
I understand your concerns about work file.
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
I am downloading this worksheet only.
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
Please see this file.
[Uploaded an Excel file]
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.
