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.

​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.    Read More

​