Countif 2 variables

tdm1103

New Member
Joined
Mar 31, 2011
Messages
19
Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
10045200060007896000034504
Sheet1
 

tdm1103

New Member
Joined
Mar 31, 2011
Messages
19
Thanks for the quick reply. Why in your formula in the first expression do you not include A1? There are instances where A1 will be greater than 0.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks

Sheet1

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1004520006000789600003450 4

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 14px;"><col style="width: 8px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
Z1=SUMPRODUCT(--(B1:X1>1),--(A1:W1=0))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

In case you want to ignore empty cells...

=SUMPRODUCT(--(B1:X1>1),--(A1:W1=0),--ISNUMBER(A1:W1))
 

tdm1103

New Member
Joined
Mar 31, 2011
Messages
19

ADVERTISEMENT

Sorry for my ignorance, but are the "--" before each range placeholders for something or is that what I use?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi,

I have a row of 24 values. I am looking to do a COUNTIF function that counts all cells that have a value that are greater than 1 and are preceded by "0". For example:

0 0 4 5 2 0 0 0 6 0 0 0 7 8 9 6 0 0 0 0 3 4 5 0

In the string of the numbers above, the COUNTIF function would return a value of 4.

Thanks
If you're using Excel 2007 or later:

=COUNTIFS(B1:X1,">1",A1:W1,0)
 

tdm1103

New Member
Joined
Mar 31, 2011
Messages
19

ADVERTISEMENT

This makes more sense, I am using Excel 2010.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Thanks for the quick reply. Why in your formula in the first expression do you not include A1? There are instances where A1 will be greater than 0.

But A1 can never be preceded by a zero, since it's in the first column of the spreadsheet.
 

tdm1103

New Member
Joined
Mar 31, 2011
Messages
19
Figured that out, I just added an "IF" statement after my COUNTIFS statement to add 1 if A1 is greater than 0.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top