Excel Report Using IF Statement?
 

I work at a crime laboratory and produce monthly stats on the types of
evidence analyzed. The master list is about a hundred items. Some are
encountered more than others. Does anyone know a function that will return a
category if the value is greater than one and skip a category if it has a
zero value. This would be the montly report. I've tried using an IF
statement to no avail. Any help would be appreciated. Thanks in advance.

Hi Marc..............

Check out Data Filter AutoFilter, and sort on your column to show all
non-blanks

Vaya con Dios,
Chuck, CABGx3


"Marc" wrote in message
...
I work at a crime laboratory and produce monthly stats on the types of
evidence analyzed. The master list is about a hundred items. Some are
encountered more than others. Does anyone know a function that will
return a
category if the value is greater than one and skip a category if it has a
zero value. This would be the montly report. I've tried using an IF
statement to no avail. Any help would be appreciated. Thanks in advance.

There's an =countif() function that might work for you.

Debra Dalgleish has a bunch of instructions on counting functions in excel at:
http://www.contextures.com/xlFunctions04.html

And for summing cells at:
http://www.contextures.com/xlFunctions01.html

Marc wrote:

I work at a crime laboratory and produce monthly stats on the types of
evidence analyzed. The master list is about a hundred items. Some are
encountered more than others. Does anyone know a function that will return a
category if the value is greater than one and skip a category if it has a
zero value. This would be the montly report. I've tried using an IF
statement to no avail. Any help would be appreciated. Thanks in advance.



--

Dave Peterson

Just another option to play around with ..

Assume the sample table below is
in Sheet1, cols A to C, data from row2 down
where the key criteria col is Value in col C
(i.e. we want only the rows where col C is 1)

Type Desc Value
Evid1 Text1 2
Evid2 Text2 1
Evid3 Text3 2
Evid4 Text4 0
Evid5 Text5 3

Using an empty col to the right, say col E?

Put in E2: =IF(AND(ISNUMBER(C2),C21),ROW(),"")

Copy down by as many rows as data is expected
in the table, say down to E500?
(can copy down ahead of expected data input)

In Sheet2
------------
With the same col headers in A1:C1, viz.:
Type Desc Value

Put in A2:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to C2, fill down by as many rows
as was done in col E in Sheet1, i.e. down to C500

Cols A to C will extract only those rows from Sheet1
where the Value col (col C) contains values 1,
and doing so without any blank rows in-between
(lines will shift up)

For the sample data in Sheet1, you'll get:

Type Desc Value
Evid1 Text1 2
Evid3 Text3 2
Evid5 Text5 3
< rest are "blanks"

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Marc" wrote in message
...
I work at a crime laboratory and produce monthly stats on the types of
evidence analyzed. The master list is about a hundred items. Some are
encountered more than others. Does anyone know a function that will
return a
category if the value is greater than one and skip a category if it has a
zero value. This would be the montly report. I've tried using an IF
statement to no avail. Any help would be appreciated. Thanks in advance.