counting number of cells which meet a certain criteria

**Laurab** · 06-18-2012, 12:37 PM

I have a spreadsheet that tracks when an item is received within the department and then when it is dealt with. If the item is not dealt with within 45mins it turns amber, 1 hour the cell will turn red, when it has been dealt with the cell turns green. I need to count how many cells are red and have a total at the top of the sheet, I dont really know enough about VBA to even try that but thought that I could do a formula that counts the cells with a set criteria. My conditional formatting for cells is:-

GREEN CONDITIONAL FORMAT =LEN($H3)>0

RED CONDITIONAL FORMAT =AND(ISBLANK($H3),$G3+ TIME(1,0,0)<NOW()-TODAY(),LEN($G3)>0)

AMBER CONDITIONAL FORMAT =AND(ISBLANK($H3),$G3 + TIME(0,45,0)<NOW()-TODAY(),LEN($G3)>0)

Have tried a few attempts but cant seem to get the criteria correct.

**dilipandey** · 06-18-2012, 01:05 PM

Hi Laurab,

Suggest you to upload a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**bonny24tycoon** · 06-18-2012, 01:12 PM

Hi There,

I dont have the workbook and Im assuming the you would need the count from column H.

try the below formula

Please Login or Register  to view this content.

Or try uploading a sample workbook

**Laurab** · 06-18-2012, 03:17 PM

Hi

Sample spreadsheet attached.

thanks

**dilipandey** · 06-18-2012, 03:34 PM

I need to count how many cells are red

You can have now() function and today() somewhere in the spreadsheet and then you can use the countif function to count the cells which meets Criteria for Red - to count how many cells are red..

Regards,
DILIPandey

<click on below 'star' if this helps>

**Laurab** · 06-18-2012, 03:57 PM

I tried that but didnt seem to work, have uploaded a sample workbook.

Thanks

**bonny24tycoon** · 06-19-2012, 04:55 AM

Hi Laurab,

You would need a supporting column or a helper column to derive your figure. I have added a helper column (you can hide it if you need your sheet to look more neat

)

Check this out.. LB_tracker1.xls

**Marcol** · 06-19-2012, 05:32 AM

Try using your C/F formulae applied to SUMPRODUCT()
e.g. Red condition

Please Login or Register  to view this content.

See this workbook

**Laurab** · 06-19-2012, 08:58 AM

Thats brilliant it worked, thanks.

counting number of cells which meet a certain criteria

LinkBack

Thread Tools

Rate This Thread

Display

counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Re: counting number of cells which meet a certain criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions