How do I use a SUMIF function when the cells to add together are not adjacent to each other?

I want to use this formula:

=IFERROR(SUMIF(Q12:Q18;Q24:Q30;Q37;(AND(P32="*volt*");P37="*volt*");(SUM(Q12:Q18;Q24:Q30);"")))

But I think I need to change the range after SUMIF. What I want is that sum everything in Q12:18 and Q24:Q30 and Q37 if both P32 and P37 contains the text "volt" (must be in both P32 and P37). Otherwise sum only Q12:Q18 and Q24:Q30.

If I need to change my formula, how should I change it?
LVL 1
hermesalphaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

ShumsDistinguished Expert - 2017Commented:
Hi,
SumIF is used only when you have cell range filled with "volt" and you want the total of "volt" from other column/columns. In your case, IF AND SUM would be helpful.
Try below:

=IF(AND(P32="volt",P37="volt"),(SUM(Q12:Q18)+SUM(Q24:Q30)+Q37),SUM(Q12:Q18)+SUM(Q24:Q30))
0
Rgonzo1971Commented:
Hi,

pls try
=IF(AND(P32="volt",P37="volt",Q37)+SUM(Q12:Q18,Q24:Q30)

Open in new window

Regards
0
Hamed NasrRetired IT ProfessionalCommented:
Try:
=IF(AND(P32="volt",P37="volt"),SUM(Q12:Q18;Q24:Q30;Q37),SUM(Q12:Q18;Q24:Q30))
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hermesalphaAuthor Commented:
I tried your formulas now, but I get a blank cell for both. By the way, I need "*volt*" (not exactly "volt" but "*volt*" and can/will contain other words also.
0
ShumsDistinguished Expert - 2017Commented:
Then try below formula:
=IF(AND(COUNTIF(P32,"*"&"volt"&"*"),COUNTIF(P37,"*"&"volt"&"*")),SUM(Q12:Q18:Q24:Q30:Q37),SUM(Q12:Q18:Q24:Q30))
0
byundtMechanical EngineerCommented:
There is no need to add Q12:Q18 & Q24:Q30 twice, so you can simplify Shums' formula to:
=SUM(Q12:Q18,Q24:Q30)+IF((COUNTIF(P32,"*volt*")+COUNTIF(P37,"*volt*"))=2,Q37)
1
Hamed NasrRetired IT ProfessionalCommented:
Modified code to try:
=IF(ISERROR(AND(FIND("volt";P32);FIND("volt";P37)));SUM(Q12:Q18;Q24:Q30);SUM(Q12:Q18;Q24:Q30;Q37))

Open in new window

0

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
Rgonzo1971Commented:
then try
=IF(COUNTIF(P32,"*volt*")*COUNTIF(P37,"*volt*"),Q37)+SUM(Q12:Q18,Q24:Q30)

Open in new window

0
hermesalphaAuthor Commented:
Shums, I tried your formula now but get a circular reference warning.

Byundt, then I tried your formula, and it works! Only when both P32 and P37 contains "volt" then it includes Q37 in the calculation.
But something strange happens: In a drop-down I have in M4 ("volt" and "auto"), when I change selection here from "volt" to "auto", then the formula in Q35 gets deleted and replaced with the calculated static value.

hnasr, your formula also works fine! But the same strange thing happens as I noted for Byundt.

Rgonzo1971, I could not get your formula to work; nothing happens when both P32 and P37 contains "volt" and I select a value in Q37.

So both Byundt's and hnasr's formulas works fine. The problem remains why the formula gets replaced by the value when changing from "volt" to "auto" in the drop-down in M4.
0
byundtMechanical EngineerCommented:
If the formula is getting changed, then you probably have a VBA event macro running. Are you able to post the workbook?
0
hermesalphaAuthor Commented:
I have this sub in the middle of a lot of other subs:

End Sub

Private Sub Worksheet_Calculate()
    Dim i As Integer
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    For i = 1 To 8
        If KeepValue(i) <> Cells(34, i + 16) And Cells(35, i + 16).HasFormula = True Then
            Cells(35, i + 16) = Cells(34, i + 16)
        End If
        KeepValue(i) = Cells(34, i + 16)
    Next i
ErrorOut:
    Application.EnableEvents = True
End Sub

But the sub doesn't work as intended: instead of keeping the formula in Q35:Y35 it keeps the value.
0
byundtMechanical EngineerCommented:
The following statement is overwriting your formula in row 35:
          Cells(35, i + 16) = Cells(34, i + 16)

Open in new window

If you want to keep the formula in row 35, and copy the value from it into row 34, you might use:
          Cells(34, i + 16).Value = Cells(35, i + 16).Value

Open in new window

If rows 34 and 35 have formulas and you want to keep them both, then you would just delete the statement altogether.
0
hermesalphaAuthor Commented:
Both row 35 and 34 have separate formulas different from each other that should be kept, but the idea with this VBA is that on row 35, I might choose to manually enter a value instead of the formula calculated value, and in case I thereafter choose to revert to the formula calculated value and hit Delete, then the old formula should be restored to this cell. This worked fine with this VBA earlier, but not now.
0
byundtMechanical EngineerCommented:
Both row 35 and 34 have separate formulas different from each other that should be kept,
If you can't copy a formula from one row (or column) to another, those formulas need to be hard-coded in the VBA.
0
hermesalphaAuthor Commented:
I can copy a formula from one row to another, but the problem is that when I select another option in that drop-down menu in cell M4, then the formula on row 35 gets replaced by the resulting value only.
0
byundtMechanical EngineerCommented:
Private Sub Worksheet_Calculate()
    Dim i As Integer
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    For i = 1 To 8
        If KeepValue(i) <> Cells(34, i + 16) And Cells(35, i + 16).HasFormula = True Then
            Cells(34, i + 16) = Cells(35, i + 16)
        End If
        KeepValue(i) = Cells(34, i + 16)
    Next i
ErrorOut:
    Application.EnableEvents = True
End Sub

Open in new window

0
Hamed NasrRetired IT ProfessionalCommented:
then the formula on row 35 gets replaced by the resulting value only
Please demonstrate the issue by uploading a working spreadsheet.
0
hermesalphaAuthor Commented:
Thanks byndt, that worked, the formula stays in place now. Only that I can not delete it like before and it stays in place, but that is another posting I need to do later.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.