Formula help please
 

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.

You could create another column with this formula:

=IF(F2=3,IF(Q20,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.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398410

Try...

=SUMPRODUCT(--(F2:F276=3),--(Q2:Q2760))

Hope this helps!

In article ,
Lucien wrote:

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.

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

"Lucien" wrote:

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.

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.



"Domenic" wrote:

Try...

=SUMPRODUCT(--(F2:F276=3),--(Q2:Q2760))

Hope this helps!

In article ,
Lucien wrote:

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.

Hi Lucien
Is this what you are looking for ??

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

HTH
Michael Mitchelson


"Lucien" wrote:

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.

Try the following formula instead...

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

Hope this helps!

In article ,
Lucien wrote:

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.



"Domenic" wrote:

Try...

=SUMPRODUCT(--(F2:F276=3),--(Q2:Q2760))

Hope this helps!

In article ,
Lucien wrote:

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.

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


"Lucien" wrote:

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.

On Tue, 23 Aug 2005 14:45:45 -0700, Lucien
wrote:

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.


=SUMPRODUCT((F2:F276=3)*(Q2:Q2760))


--ron

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.

"Ron Rosenfeld" wrote:

On Tue, 23 Aug 2005 14:45:45 -0700, Lucien
wrote:

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.


=SUMPRODUCT((F2:F276=3)*(Q2:Q2760))


--ron

On Wed, 24 Aug 2005 09:39:04 -0700, Lucien
wrote:

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.


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 Rosenfeld" wrote:

On Tue, 23 Aug 2005 14:45:45 -0700, Lucien
wrote:

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.


=SUMPRODUCT((F2:F276=3)*(Q2:Q2760))


--ron


--ron

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.




"Ron Rosenfeld" wrote:

On Wed, 24 Aug 2005 09:39:04 -0700, Lucien
wrote:

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.


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 Rosenfeld" wrote:

On Tue, 23 Aug 2005 14:45:45 -0700, Lucien
wrote:

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.


=SUMPRODUCT((F2:F276=3)*(Q2:Q2760))


--ron


--ron

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

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

In article ,
Lucien wrote:

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.

On Thu, 25 Aug 2005 09:15:06 -0700, Lucien
wrote:

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.

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:Q2760)*ISNUMBER(Q2:Q2 76))


--ron

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!!!


"Ron Rosenfeld" wrote:

On Thu, 25 Aug 2005 09:15:06 -0700, Lucien
wrote:

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.

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:Q2760)*ISNUMBER(Q2:Q2 76))


--ron

In article ,
Lucien wrote:

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

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?

On Tue, 30 Aug 2005 14:37:02 -0700, Lucien
wrote:

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!!!

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:Q2760))


--ron

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:Q2760))

Dunno, simpler still may be

=COUNT((F2:F276=3)*(1/Q2:Q2760))

which uses Q2:Q276 as-is.

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


"Ron Rosenfeld" wrote:

On Tue, 30 Aug 2005 14:37:02 -0700, Lucien
wrote:

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!!!

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:Q2760))


--ron