Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I'm trying to write a formula in column G that basically says "Sum everything in column F where the value is less than or equal to 50, and where the value in column G equals the value of the cell that is 1 cell above the current cell, and where the value of the cell 1 above the current cell equals the value of the cell 2 cells above the current cell". When I drag my formula down from G3, it does not seem to be working as I expected. Every cell is showing up as 0. How should I fix my formula below?

```
=SUMIFS(F:F,G:G,G2,F:F,"<=50",G:G,G2=G1)
```

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FALSE * FALSE = 0

TRUE * FALSE = 0

FALSE * TRUE = 0

TRUE * TRUE = 1

The result is finally multiplied by the value in column F to get the answer.

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial=SUMPRODUCT((F3:F100<=50),

Kevin

=SUMIFS(F:F,G:G,G2,F:F,"<=

=SUMIFS(F3:F100,G3:G100,"<

Array formulas are strange too. Choose your poison and move on to the next project ;-)

Also, the formula I posted above won't work:

=SUMPRODUCT((F3:F100<=50),

It should be:

=SUMPRODUCT((F3:F100<=50)*

Kevin

I don't believe the SUMIFS version will work

In

=SUMIFS(F3:F100,G3:G100,"<

the second element is the result of.....

=SUMIFS(F3:F100,G3:G100,"<

etc.

Exactly which value you see in the cell can depend on

regards, barry

Kevin

Are you suggesting array entering in a

Either way it doesn't change my main point - I don't think you can use SUMIFS for this type of problem - you'll get different results using SUMIFS, and not the same results as the SUMPRODUCT version that works.

regards, barry

My current interpretation of the question is that you want to, on each row and in column G, sum all of the values in column F where the value in F is less than or equal to 50 and the value in column G on that same row is equal to the value in column G in the above two rows.

Your original formula references all of column F and column G. That results in a circular reference (you are evaluating over the same cell in column G that you are trying to calculate.) The only way this makes any sense is if we limit the bottom of the evaluation area to the row above the row being calculated.

This formula, placed in cell G4 and then copied down will do that:

=SUMPRODUCT((F$3:F3<=50)*(

Further consideration of the question leads me to this question back to you: what the heck are you trying to do? The results of this formula don't seem to provide any real value as you are summing based on the sums in other rows.

Are you sure you posed the question correctly?

And, just to be clear, Barry is correct, SUMIFS cannot do what you want.

Kevin

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

Kevin