MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 2000 Sumif/Countif

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

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

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

Any offers?



9 Answers Found

Answer #1    Answered By: Akina Suzuki     Answered On: Sep 02

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


Answer #2    Answered By: Abraham Lopez     Answered On: Sep 02

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?

Answer #3    Answered By: Eula Armstrong     Answered On: Sep 02

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.

Answer #4    Answered By: Samuel Evans     Answered On: Sep 02

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
heading the same as Cell.

Do you think I'm reading correctly here?

Answer #5    Answered By: Fergus Jones     Answered On: Sep 02

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

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?

Answer #6    Answered By: Dashiell Jones     Answered On: Sep 02

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



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.

Answer #7    Answered By: Emily Campbell     Answered On: Sep 02

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

A quick and easy cheat or should we say alternative.

Use 3 cells with the formula:


replace the mysubtotal1 with the appropriate cell reference.

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

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

Create a named range say MySubtotals

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


Answer #8    Answered By: Brooke Robertson     Answered On: Sep 02

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

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

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

Answer #9    Answered By: Trina King     Answered On: Sep 02

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

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