Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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

heading the same as Cell.

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

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

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.

Related Topics:

- Excel VBA Problem in excel 2000
- Convert code from excel 2000 vba to excel 2003 vba
- VBA Function in Excel 2000
- Excel 2000: Time totaling variable 'overflows' at 24:00 and display
- Excel 2000: Modifying the date with DTPicker does not fire AfterUpd
- excel 2000 - EntireColumn
- Excel 2000 - Select
- excel 2000 - Wierd Paste Problem
- Excel 2000 - button on sheet
- Excel 2000 - One place for code
- Excel 2000 - FreezPanes
- Excel 2000 Picking up sheet name
- Excel 2000 Error 1004
- Is there a way to have an input form address a hidden worksheet in excel 2000
- Excel 2000 Conditional formatting
- ms excel 2000 questions
- excel 2000 question
- excel 2000 - Delete confirmation
- Excel 2000 - Names
- Excel 2000 - Copy Sheet
- CountIF Function
- Need help for COUNTIF
- Using a Defined Variable Range in a VBA SumIf
- How to add several conditions for formula "SUMIF"
- SQL Server 2000 Notification Services