[Solved] SUMIFS but same row not column

[ckciarakennedy]

I want to sum certain cells if other cells in the same row meet certain criteria.
For each week there are four data points: SoW, #, L and C.
If SoW = P, F or R (or is not blank), I want to sum up all of the corresponding L data points.
So for the attached file, I want to get a result of 15 (=G3+AQ3+AU3)
Thanks

 

[Lutfor Rahman Shimanto]

I want to sum certain cells if other cells in the same row meet certain criteria.
For each week there are four data points: SoW, #, L and C.
If SoW = P, F or R (or is not blank), I want to sum up all of the corresponding L data points.
So for the attached file, I want to get a result of 15 (=G3+AQ3+AU3)
Thanks

View attachment 59

Hello Ckciarakennedy,

Welcome to our website! We appreciate your question being posted on the platform. There is a way to solve the problem you submitted about adding up all the corresponding L data points if the SoW column has the letters P, F, or R. (or is not blank). I've provided a formula utilizing the IF, SUM, INDIRECT, and SUMIFS functions that precisely addresses the problem you posed.
Formula: =IF(E3<>"",SUM(INDIRECT({"G3","K3","O3","S3","W3","AA3","AE3","AI3","AM3","AQ3","AU3","AY3"})),SUMIFS(INDIRECT({"G3","K3","O3","S3","W3","AA3","AE3","AI3","AM3","AQ3","AU3","AY3"}),E3,{"P","F","R"}))

Please let me know if you have any other questions or concerns, and I'll be happy to help you further.

Best regards,
Lutfor Rahman Shimanto

 

[ckciarakennedy]

Hello Ckciarakennedy,

Welcome to our website! We appreciate your question being posted on the platform. There is a way to solve the problem you submitted about adding up all the corresponding L data points if the SoW column has the letters P, F, or R. (or is not blank). I've provided a formula utilizing the IF, SUM, INDIRECT, and SUMIFS functions that precisely addresses the problem you posed.
Formula: =IF(E3<>"",SUM(INDIRECT({"G3","K3","O3","S3","W3","AA3","AE3","AI3","AM3","AQ3","AU3","AY3"})),SUMIFS(INDIRECT({"G3","K3","O3","S3","W3","AA3","AE3","AI3","AM3","AQ3","AU3","AY3"}),E3,{"P","F","R"}))

Please let me know if you have any other questions or concerns, and I'll be happy to help you further.

Best regards,
Lutfor Rahman Shimanto

Thank you so much for your reply. That is a HUGE help. I've actually been working on the file more - is it possible for the formula to work if i dont know the first cell that is not blank? See attached. Can I use a formula for all weeks of the year?

 

[Lutfor Rahman Shimanto]

Thank you so much for your reply. That is a HUGE help. I've actually been working on the file more - is it possible for the formula to work if i dont know the first cell that is not blank? See attached. Can I use a formula for all weeks of the year?

View attachment 67

Dear Ckciarakennedy,

I'm delighted to hear that the solution I offered for your earlier issue was helpful. After reviewing your query, I understand that you need to apply the formula for all weeks of the year, and you are not sure about the first cell that is not blank. Excel VBA is a fantastic approach in such circumstances. I have solved the problem and have attached the workbook.

To demonstrate this point, I calculated the summation of L Point values satisfying the criteria mentioned previously for Project 1. I created a button and assigned the macro to it. Hopefully, the code will solve your issue. Modify the code when needed, and good luck.

Sub SumLData()

Dim lastColumn As Long
Dim i As Long
Dim j As Long
Dim SoW As String
Dim LData As Long
Dim totalLData As Long

lastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
totalLData = 0

For i = 3 To lastColumn Step 4
    SoW = Cells(3, i).Value
    If SoW = "P" Or SoW = "F" Or SoW = "R" Or SoW <> "" Then
       LData = Cells(3, i + 2).Value
       totalLData = totalLData + LData
    End If
Next i

Cells(10, 5).Value = totalLData

End Sub

Thank you for choosing our platform. If you face any further issues, please do not hesitate to let us know.

Best regards,
Lutfor Rahman Shimanto