Formula help please

L

Lucien

I have a large spreadsheet that includes the following detail.
Column F has numerical values of either 1, 2, or 3 ranging from F2:F276.
Column Q has varying numerical values ranging from Q2:Q276.

I need one formula to count with this criteria:
Column F is 3 and
Column Q > 0

So, I need it to count how many occurrences a line has both, a "3" in column
F and, the qty in column Q is greater than zero.

Any help would be greatly appreciated.
 
E

Excel_Geek

You could create another column with this formula:

=IF(F2=3,IF(Q2>0,1,0),0)

This will mark each row with a 1 if both conditions are met. Then
simply sum up that third column.

I'm sure there are many other ways to do it, but this is quick, fast,
simple.
 
M

Marvin P. Winterbottom

you need a 3rd column with this formula:
=if(f2=3,if(q2>0,1,0),0)
that column will have a 1 if f=3 and q>0, or else 0, so just sum it
 
L

Lucien

Domenic,

This formula gave me the same total as just counting F2:F276=3. It did not
give me the total of -- if F2:F276=3 then how many of Q2:Q276 are >0.
 
M

Michael

Hi Lucien
Is this what you are looking for ??

=COUNTIF(A1:A8,3)+COUNTIF(B1:B8,">0")

HTH
Michael Mitchelson
 
D

Domenic

Try the following formula instead...

=SUMPRODUCT(--(F2:F276=3),--(ISNUMBER(Q2:Q276)),--(Q2:Q276>0))

Hope this helps!
 
M

Michael

Sorry
I used my sample cell locations instead of yours. Substitute yours for mine.
Regards
Michael Mitchelson
 
R

Ron Rosenfeld

I have a large spreadsheet that includes the following detail.
Column F has numerical values of either 1, 2, or 3 ranging from F2:F276.
Column Q has varying numerical values ranging from Q2:Q276.

I need one formula to count with this criteria:
Column F is 3 and
Column Q > 0

So, I need it to count how many occurrences a line has both, a "3" in column
F and, the qty in column Q is greater than zero.

Any help would be greatly appreciated.
Click to expand...


=SUMPRODUCT((F2:F276=3)*(Q2:Q276>0))


--ron
 
L

Lucien

All of the formula help from every one has been great. However, I am not
getting the correct answer based on the data. Maybe it is in my explanation
of what I need.
I want to search through F2:F276 for any cell that contains a 3.
Let's say that identifies 50 cells.
Then, from only those 50 cells, which of them have a qty >0 in column Q.
I want it to count the number of occurrences that this happens in one formula.

I apologize for any confusion I have caused and at the same time appreciate
the help received so far.
 
R

Ron Rosenfeld

All of the formula help from every one has been great. However, I am not
getting the correct answer based on the data. Maybe it is in my explanation
of what I need.
I want to search through F2:F276 for any cell that contains a 3.
Let's say that identifies 50 cells.
Then, from only those 50 cells, which of them have a qty >0 in column Q.
I want it to count the number of occurrences that this happens in one formula.

I apologize for any confusion I have caused and at the same time appreciate
the help received so far.
Click to expand...

That is exactly what the formula I supplied (and some of the others) will do.

What do you get as a result if you use my formula?

What do you get if you use the formula =COUNTIF(F2:F276,3) ?

If you get the same results, can you identify a row which has a 3 in column F
and a 0 or negative number in column Q?






--ron
 
L

Lucien

Ron,

Using the formula you and others supplied, my count is 181.
Using =COUNTIF(F2:F276,3) I also get 181.
There are a toatal of 115 rows that have both a 3 in column F and a number
greater than zero in Q.
There are 66 rows that have a 3 in column F and a zero or negative # in
column Q.

Another strange thing: I put an auto filter on the spreadsheet and filtered
column F to pull up all the "3"s. =181.
Then I tried a custom filter on column Q to show everything greater than 0.
No result. ...when there is actually 115 lines.
 
D

Domenic

Make sure that Column Q is not formatted as 'Text'. What does the
following formula return...

=COUNTIF(Q2:Q276,"?*")
 
R

Ron Rosenfeld

Ron,

Using the formula you and others supplied, my count is 181.
Using =COUNTIF(F2:F276,3) I also get 181.
There are a toatal of 115 rows that have both a 3 in column F and a number
greater than zero in Q.
There are 66 rows that have a 3 in column F and a zero or negative # in
column Q.

Another strange thing: I put an auto filter on the spreadsheet and filtered
column F to pull up all the "3"s. =181.
Then I tried a custom filter on column Q to show everything greater than 0.
No result. ...when there is actually 115 lines.
Click to expand...

OK, the problem is that, contrary to what you wrote, the values in Column Q are
NOT numbers. Rather they are TEXT which happen to look like numbers.

Use this formula instead:

=SUMPRODUCT((F2:F276=3)*(Q2:Q276>0)*ISNUMBER(Q2:Q276))


--ron
 
L

Lucien

Well, I double checked column Q and the cells are formatted as General. I
tried fomatting all the cells to Number, no change in the formulas. Ron your
last formula gave me a result of 0. I tried the formula before I reformatted
the cells to Number.

Domenic, your last formula gave me a result of 275, which is the total
number of cells in column Q.

still struggling in Tx!!!
 
D

Domenic

Lucien said:
Domenic, your last formula gave me a result of 275, which is the total
number of cells in column Q.
Click to expand...

That means that your numbers are being recognized as 'Text'. Try the
following...

1) Select/highlight an empty cell

2) Edit > Copy

3) Select/highlight Column Q

4) Edit > Paste Special > Add > Ok

Does this help?
 
R

Ron Rosenfeld

Well, I double checked column Q and the cells are formatted as General. I
tried fomatting all the cells to Number, no change in the formulas. Ron your
last formula gave me a result of 0. I tried the formula before I reformatted
the cells to Number.

Domenic, your last formula gave me a result of 275, which is the total
number of cells in column Q.

still struggling in Tx!!!
Click to expand...

Your result makes it clear that what you think are numbers are really text
representations of numbers.

By the way, checking the Format does NOT give you any information as whether
the values are text or are numbers.

One way to tell if a value in Q2 is a number or is text is with the formula
=ISNUMBER(Q2) or ISTEXT(Q2)

Probably the simplest solution will be to convert the values to numbers. One
way to do this is to:

1. Select an empty cell.
2. Edit/Copy
3. Select Q2:Q276
4. Edit/Paste Special/ Operation: Add

Then try the first formula I gave you again:

=SUMPRODUCT((F2:F276=3)*(Q2:Q276>0))


--ron
 
H

Harlan Grove

Ron Rosenfeld wrote...
....
Your result makes it clear that what you think are numbers are really text
representations of numbers. ....
Probably the simplest solution will be to convert the values to numbers. One
way to do this is to:

1. Select an empty cell.
2. Edit/Copy
3. Select Q2:Q276
4. Edit/Paste Special/ Operation: Add

Then try the first formula I gave you again:

=SUMPRODUCT((F2:F276=3)*(Q2:Q276>0))
Click to expand...

Dunno, simpler still may be

=COUNT((F2:F276=3)*(1/Q2:Q276>0))

which uses Q2:Q276 as-is.
 
L

Lucien

Well, it finally worked!! Once I (finally) converted the values to numbers,
the formulas gave me the results I have been looking for!

Thank you very much for all the help, I learned alot!

Thanks,
Chris
 
Top