lookup and count if meeting criteria

**reghu** · 09-07-2010, 05:16 AM

Hi
I want to use to lookup a value in another table and count the number of values which meet the criteria .

example sheet attached.

appreciate help
reghu

**Marcol** · 09-07-2010, 07:22 AM

Try this workbook

With Sheet "report"
In C4

Please Login or Register  to view this content.

In D4

Please Login or Register  to view this content.

In E4

Please Login or Register  to view this content.

Select C4:E4
Drag/Fill Down

That should call in the cavalry......

Hope this helps

**reghu** · 09-08-2010, 04:57 AM

hi marcol,thanks for the reply, the formula is fine if there are only 1-5 values, but in reality i have to values from 1-36 and i split them into 4 (<=10, 11 to 20, 21 to 29 and >29) the formula would be ok for the first and last values but for the other two it would be too large, besides it might have decimal points as well..since its calculated result which is rounded off to the nearest decimal.

**Marcol** · 09-08-2010, 12:27 PM

Try this

In D4

Please Login or Register  to view this content.

Drag/Fill Down

This will work for all versions of excel

If you are confident that you will only be working with 2007 and later you could use COUNTIFS. I have included examples in this workbook.

(I don't know if that is true with Macs)

Hope this helps.

**reghu** · 09-09-2010, 06:36 AM

thanks...i think this will work.. will check it out..

**Marcol** · 09-09-2010, 07:18 AM

Glad to have helped.

You might be able to use this in "report" D4

Please Login or Register  to view this content.

This would mean that if you criteria Usage > 5 changes to say Usage > 6 then the crieria Usage 2>4 will automatically change to Usage 2 > 5 and so on.