Search:

Excel 2000 Sumif/Countif

Asked By: Dukker    Date: Sep 02    Category: MS Office    Views: 895

I have data as follows...
D H
--------------------
5 A 0
6 B 0
7 C 0
8 Sub Total 0
9
10 D 1
11 E 2
12 F 3
13 Sub Total 6
14
15 G 0
16 H 0
17 I 0
18 Sub Total 0
19
20 Total =SUMIF(OFFSET(D2,0,0,ROW()-2,1),"=Sub
Total",OFFSET(H2,0,0,ROW()-2,1))

I need to count if any of the "Sub Total" rows are 0.

Any offers?

Share:

There is an excellent page here which shows several ways to do this:

http://www.ozgrid.com/Excel/count-if.htm

Am I right in thinking that these examples mean that I either have to have a
"range of cells" - one cell in this case - specifying a criteria, or set up
an array?

No. The D-Functions require only that you have set up proper column
headings. The array formulae examples need to be entered with array
brackets (CTRL+SHIFT+ENTER) but the D-functions don't.

I have space rows all over the place... would that affect the headings.
My headings are on row 4 but there is nothing in rows 5 and a space row
after each sub total.

As I read the description of DCountA... The 3 parameters could be
(Range1,Cell,Range2)... where Cell is the top column of the data being
searched and Range2 is a range of cells containing criteria that has a

Do you think I'm reading correctly here?

For the same problem with slightly diff rows but still lots of space
rows....

This works in H26...
=SUMPRODUCT((D6:D22="sub total")*(H6:H22=0))

This doesn't work in H27...
=SUMPRODUCT((OFFSET(D6,0,0,ROW()-5,1)="sub total")*(H6:H22=0))

You can see I'm trying to use the Offset function to a specific cell and
inluding Row() to return a range.

Any idea wha'ts happening here?

es, your syntax in the OFFSET is the wrong way around. From Excel Help:

Syntax

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise,
OFFSET returns the #VALUE! error value.

Rows is the number of rows, up or down, that you want the upper-left
cell to refer to. Using 5 as the rows argument specifies that the
upper-left cell in the reference is five rows below reference. Rows can
be positive (which means below the starting reference) or negative
(which means above the starting reference).

Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to
the right of reference. Cols can be positive (which means to the right
of the starting reference) or negative (which means to the left of the
starting reference).

Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.

I am repeating a message that I sent earlier, but doesn't appear on my
listing.

A quick and easy cheat or should we say alternative.

Use 3 cells with the formula:

=if(mysubtotal1=0,1,0)

replace the mysubtotal1 with the appropriate cell reference.

If you do not want thesse displayed, colour the font white or the same as

Or place the cells off to the right away from the main data.

Remember to set your print area, so Excel does not include them in your
print-outs.

Create a named range say MySubtotals

In the cell that you want to indicate the number of zero subtotals place the
formula:

=sum(MySubTotals)

There will be more elegant ways to accomplish this, but as a quick fix do
the following.

In 3 cells that are not being used, Just enter the formula
=if(subtotal1=0,1,0)

Change subtotal 1 for each of the subtotals/cell references concerned.

You can simply do a =sum(subtotal1:subtotal3) to accomplish your target
count.

if you do not want the hide the new
calculations, just set the font color to white (assuming you have a white
background.

Didn't find what you were looking for? Find more on Excel 2000 Sumif/Countif Or get search suggestion and latest updates.