Help
I have three rows each extracting specific information from a master sheet. In the mastersheet I have a formula in each cell that shows the word ‘overdue’ at the end of the month. Throughout the month I overwrite this formula with a number sequence. The mastersheet is working perfectly.
=IF(TODAY()<$K$3,"",IF(TODAY()>EOMONTH($K$3,0),"OVERDUE"))
I now need to extract the following info and portray them in a chart;
Total ((for specific department) formula used,
=COUNTIF('MAP''s'!$A$5:$A$42,"*corporate*") This is working correctly
Not completed (those cells which show ‘overdue’ for any specific month)
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"overdue") This is working correctly but shows
Completed (those cells that show a number) This is where I am having trouble.
I have tried the following formulas which I am sure are not working correctly because the mastersheet has the overdue formula in each cell which contains both numbers and text. (as shown at top)
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"<>")
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"isnumber")
=COUNTIF('MAP''s'!$A$5:$A$42,"*corporate*"+COUNT('MAP''s'!J$5:J$42,"isnumber"))
I can do a simple formula for completed, =sum(B4-B5), but this will show a total number for upcoming months which throws out the chart.
If anyone has any suggestions on how I can overcome this lasts part it would be very appreciated
I have three rows each extracting specific information from a master sheet. In the mastersheet I have a formula in each cell that shows the word ‘overdue’ at the end of the month. Throughout the month I overwrite this formula with a number sequence. The mastersheet is working perfectly.
=IF(TODAY()<$K$3,"",IF(TODAY()>EOMONTH($K$3,0),"OVERDUE"))
I now need to extract the following info and portray them in a chart;
Total ((for specific department) formula used,
=COUNTIF('MAP''s'!$A$5:$A$42,"*corporate*") This is working correctly
Not completed (those cells which show ‘overdue’ for any specific month)
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"overdue") This is working correctly but shows
Completed (those cells that show a number) This is where I am having trouble.
I have tried the following formulas which I am sure are not working correctly because the mastersheet has the overdue formula in each cell which contains both numbers and text. (as shown at top)
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"<>")
=COUNTIFS('MAP''s'!$A$5:$A$42,"*corporate*",'MAP''s'!I$5:I$42,"isnumber")
=COUNTIF('MAP''s'!$A$5:$A$42,"*corporate*"+COUNT('MAP''s'!J$5:J$42,"isnumber"))
I can do a simple formula for completed, =sum(B4-B5), but this will show a total number for upcoming months which throws out the chart.
If anyone has any suggestions on how I can overcome this lasts part it would be very appreciated
