Need help with Multiple IF and AND statements

Hi all, I have been working on this for hours with no luck. There must be something I am missing. I have 1 spreadsheet with two tabs. Tab 1 "Issues Register" contains all the raw data entry ...

excel

Formulas and Functions

NikolinoDE
Nov 01, 2024
salesSZ
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.

NikolinoDE

Platinum Contributor

Nov 01, 2024

salesSZ

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.

salesSZ

Copper Contributor

Nov 02, 2024

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.

Forum Discussion

Need help with Multiple IF and AND statements

Resources