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

=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.

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.

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.

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.

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

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.

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