Forum Discussion
Need help with Multiple IF and AND statements
1)Solution proposal with COUNTIFS
=COUNTIFS('Issue Register'!C:C, ">=" & A2, 'Issue Register'!C:C, "<=" & B2, 'Issue Register'!M:M, "04-Item Closed")
This method is efficient for summing data based on multiple conditions and works well in your case.
2)Solution proposal with SUMPRODUCT
=SUMPRODUCT(( 'Issue Register'!C:C >= A2 ) * ( 'Issue Register'!C:C <= B2 ) * ( 'Issue Register'!M:M = "04-Item Closed" ))
Avoid using entire columns (e.g., C:C), especially with large datasets. Limit the ranges (e.g., C3:C1000), as it can improve performance.
My answers are voluntary and without guarantee!
Hope this will help you.
1)Solution proposal with COUNTIFS
=COUNTIFS('Issue Register'!C:C, ">=" & A2, 'Issue Register'!C:C, "<=" & B2, 'Issue Register'!M:M, "04-Item Closed")
This method is efficient for summing data based on multiple conditions and works well in your case.
2)Solution proposal with SUMPRODUCT
=SUMPRODUCT(( 'Issue Register'!C:C >= A2 ) * ( 'Issue Register'!C:C <= B2 ) * ( 'Issue Register'!M:M = "04-Item Closed" ))
Avoid using entire columns (e.g., C:C), especially with large datasets. Limit the ranges (e.g., C3:C1000), as it can improve performance.
My answers are voluntary and without guarantee!
Hope this will help you.
- Ok, so I had one teeny error, "04-Item Closed" was actually "04-Item closed". The formula now works perfectly....!!! Thank you ever so much. I am very very grateful.
- Hi Nikolino,
I have tried both those formulas, but all cells show "0" as the result. There are only 100 rows so its not a big spreadsheet. I have 1 cell in the Issues Register sheet that has the "04-Item Closed" displaying, so was hoping to have at least a 1 show somewhere.
Do you have any more ideas? I really do appreciate your help here. 🙂 Thank you. I am racing off to nightshift now but have taken my laptop with me. Will try this tonight and let you know how I go! Thanks so much.
