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