A little help re: Count Formulas

AndrewRossington

Board Regular
What I need to do is count how many entries in a column have a cell on their respective row that meets a certain criteria.

Is there something like "sumproduct" but for counting... maybe "countproduct"? Or is it not that simple?

Any help would be much appreciated.

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
COUNTIF?

I thought Countif only checked criteria of cells you're counting...?

How could I get it to count one lot of cells whilst checking both the counted and an additional column?

vlookup?

or a combination of the two?

What I need to do is count how many entries in a column have a cell on their respective row that meets a certain criteria.

Is there something like "sumproduct" but for counting... maybe "countproduct"? Or is it not that simple?

Any help would be much appreciated.

What are the criteria and the ranges that must meet them?

It doesn't matter which column you count does it? The answer will be the same eg

apples 10
oranges 20
apples 10
lemons 40

If you want to count column B that has apples just do the countif on column a. It gives the same result (2). Or do you want to sum 10+10? In which case use SUMIF

What are the criteria and the ranges that must meet them?

I need to know how many cells in column E that have data in which also have a cell on the same row in column G with "Nursery" entered.

=SUMPRODUCT(--(E1:E1000<>""),--(G1:G1000="Nursery"))

=SUMPRODUCT(--(E1:E1000<>""),--(G1:G1000="Nursery"))

I've tried this but it's displaying NUM# in the cell.

Won't this formula sum the cells instead of count them?

Won't this formula sum the cells instead of count them?

No, since we didn't include a range to be summed.

Did I correctly understand your original question - you want to count every instance where column E contains data and column G contains the word "Nursery"?

Replies
4
Views
134
Replies
3
Views
121
Replies
3
Views
441
Replies
7
Views
125
Replies
3
Views
152

1,217,498
Messages
6,136,988
Members
450,037
Latest member
Tao86

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.

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

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