conditional counting

[ipsdan]

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.

 

[ken2step]

=sumproduct(--(D1:D100<>""),--(A1:A100="A"))

 

[ipsdan]

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]

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]

Also...

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

would count text-valued and numeric cells.

 

[joezeppy]

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]

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]

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

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

 

[joezeppy]

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

ref. successful post:

http://www.mrexcel.com/board2/viewtopic.php?t=129447

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