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