conditional counting

ipsdan

Board Regular
Joined
Aug 31, 2004
Messages
74
Any help appreciated...
I would like to count the number of cells in D:D that contain data, i.e. aren't blank, when A:A equals "A".
Thanks in advance.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ipsdan

Board Regular
Joined
Aug 31, 2004
Messages
74
Ken,

Thanks for the information. I would be grateful if you (or someone else!) could explain what the '--' means in that formula. What purpose does it serve?
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
the dashes coerce the True and False answers to 1 and 0 so that Sumproduct can count them. Ativate the cell the formula is in and hit shift-F3. Remove the -- and see what happens. This will explain easier maybe.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Also...

=COUNT(Range)+COUNTIF(Range,"?*")

would count text-valued and numeric cells.
 

joezeppy

Board Regular
Joined
Feb 7, 2005
Messages
72
Counting "Until?" Is that possible?

I think I have a similar request except that I would like to develop a formula that counts the number of blank cells up until it hits a cell with data and gives me that count.

Is that possible?

So wherever I place this formula, it would count the number of cells above it UNTIL it reaches a cell with data and then stop and just return that count of the number of blank cells.

Any help or thoughts appreciated.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

ipsdan said:
Any help appreciated...
I would like to count the number of cells in D:D that contain data, i.e. aren't blank, when A:A equals "A".
Thanks in advance.

=COUNT(D:D)+COUNTIF(D:D,"?*")

which would only include numbers and non-zero length text values.

Otherwise:

=COUNTA(D:D)

Addendum. I missed the condition imposed on column A, hence the above inappropriate formula.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: Counting "Until?" Is that possible?

joezeppy said:
I think I have a similar request except that I would like to develop a formula that counts the number of blank cells up until it hits a cell with data and gives me that count.

Is that possible?

So wherever I place this formula, it would count the number of cells above it UNTIL it reaches a cell with data and then stop and just return that count of the number of blank cells.

Any help or thoughts appreciated.

Doesn't what I suggested in your proper thread fit the bill?
 

joezeppy

Board Regular
Joined
Feb 7, 2005
Messages
72
Dear Sir,

It worked.

I apologize for (I guess) a bit of cross posting. Didn't mean to do that.

I just ran across this gentleman's post after I did mine and the problem sounded similar.

Your solution worked great. I had to tweak it a bit in a very simple way (took out the -1 as the structure of my spread didn't need that).


I'm very impressed.

Now I'm going to have to really try to read that to see how you did it. The only way I ever understand formulas and functions is to say them out loud. LOL.

Thank you very much.

Best Regards,

Joe
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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