Forum Discussion
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 information for each incident, Tab 2 "Working Formula Sheet" is where I am generating all my "working" formulas which will be used to prepare graphs, based on info from Tab 1.
In Tab 1, Column C displays the date the incident was created, and Column M displays the progress bar for the incident, e.g. 01-Item Started, 02-Awaiting Information, 03-Up for Approval and 04-Item Closed etc.
I am trying to work out yearly and monthly totals, risk level totals and progress totals etc. I've been able to work out formulas for all these except the progress totals.
In Tab 2, I have Column A, Row 2 which displays the first date of January, and Column B, Row 2 which displays the last date of January, then Column A, Row 3 and Column B, Row 3 for Feb and so on. I also have in Column A, Row 28 04-Item Closed.
To identify how many entries were created for a specific month, I used this formula:
=(IF(AND('Issue Register'!C3>=$A$2, 'Issue Register'!C3<=$B$2), "1", "0"))+0
This has worked well.
What I'm now trying to work out is:
How many entries in each month were "closed", eg has the cell value of 04-Item Closed".
I tried to use the formula above, and added in an extra IF statement:
=(IF(AND('Issue Register'!C3>=$A$2, 'Issue Register'!C3<=$B$2, 'Issue Register'!M3=$A$28), "1", "0"))+0 but it doesnt work.
What am I missing please?
Thanks.
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.
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.
- 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. 🙂 - 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.
