CountIF Formula help

[demodren]

Hi all, I am looking for some basic help on some count formulas.. any help would be much appreciated..Thanks so much

I am using:

=COUNTIF(W17:W1500,"<>0")

I am trying to do couple of things:
1) Count number of rows with values <> 0
it seems it counts rows that are blank as a value as well. Is there a way to avoid it? I am specifically looking for it to give me a count of how many cells do not have the value 0. (W17:W1500 is just a range, my total rows are only 1000, I think its scanning all 1500 rows and adding count to blank cells)

2) Count number of rows with values <-1 OR >1
=COUNTIF(W17:W1500,">1") OR =COUNTIF(W17:W1500,"-<1")
is there a way to combine these?

Thanks so much

 

[Joe4]

There are a myriad of different ways, but here is one (and it is really using the same logic for both of your questions):

1. =COUNTIF(W17:W1500,">0")+COUNTIF(W17:W1500,"<0")

2. =COUNTIF(W17:W1500,">1")+COUNTIF(W17:W1500,"<-1")

 

[demodren]

thanks Joe, that did the trick!

Is there a way also summing the values in those cells? based on the same characterstics, instead counting just tell the sums?

thanks so much

 

[Joe4]

Is there a way also summing the values in those cells? based on the same characterstics, instead counting just tell the sums?

I'll give you one guess what function name would be
**cue Jeopardy! music here**
...
...

If you said, SUMIF, you are a winner!
(just having a little fun on a Friday afternoon!)

 

[demodren]

sorry please disregard the previous post. There are 2 other steps that I am trying to complete and curiosu if there a way to perform this.

1) Based on the rows in column W where cells <> 0, sum up all the values in column Z for those cells.. Basically my column W tells me how many units I have missing of the product and column Z tells me monetary impact.

2) insteaf of counting # of cells, sum those values for below formula

 =COUNTIF(W17:W1500,">1")+COUNTIF(W17:W1500,"<-1")

Sorry I should of been more clearier on the whole picture.. Any help is much appreciated Thanks so much

 

[demodren]

duh, ok so step 2 is all good. SUMIF works! for some reason i was using SUMPRODUCT.....my mistake

 

[Joe4]

You could use SUMPRODUCT too, but it is more complex and not necessary in this case.

 

[demodren]

I was getting ahead of myself what about the 1) as here I want to sum the cells from column based on results from other column.. would it be COUNTIF combined with SUMIF?

=COUNTIF(W17:W1500,">0")+COUNTIF(W17:W1500,"<0")

"Count number of rows with values <> 0
it seems it counts rows that are blank as a value as well. Is there a way to avoid it? I am specifically looking for it to give me a count of how many cells do not have the value 0. (W17:W1500 is just a range, my total rows are only 1000, I think its scanning all 1500 rows and adding count to blank cells)"

 

[T. Valko]

sorry please disregard the previous post. There are 2 other steps that I am trying to complete and curiosu if there a way to perform this.

1) Based on the rows in column W where cells <> 0, sum up all the values in column Z for those cells.. Basically my column W tells me how many units I have missing of the product and column Z tells me monetary impact.

2) insteaf of counting # of cells, sum those values for below formula

 =COUNTIF(W17:W1500,">1")+COUNTIF(W17:W1500,"<-1")

Sorry I should of been more clearier on the whole picture.. Any help is much appreciated Thanks so much

For #1...

Book1
	W	Z	AA	AB
2	11	47	_	204
3	_	69	_	_
4	25	5	_	_
5	0	24	_	_
6	10	19	_	_
7	0	50	_	_
8	0	16	_	_
9	65	39	_	_
10	-10	25	_	_
Sheet1

This formula entered in AB2:

=SUMIF(W2:W10,"<>0",Z2:Z10)

Note that this will evaluate empty cells as being <>0 and will sum any values that correspond to the empty cells as in this case with Z3 being included in the sum.

Not sure what you want to do with #2. Can you explain that in words?

 

[demodren]

thats perfect! even better if its picks upblank ones.. thanks so much.. I am good on all the points.. sorry for some confusion