COUNTA with a condition in another column?

[Big Monkey]

OK I've run into a booger here.

I have a simple formula now like

=COUNTA(A2:A65536)

What I want to do is get the same result, but only of column I on the same row contains "mytext"

So:

Col A
1
1
1
1

This would equal to 4 with my formula.

Col A Col B
1 mytext
1
1 mytext
1

How would I write the formula for the above to give me a value of 2?

I tried using a sumproduct, but that is totaling the numbers in column A, and I only want the counta values.

 

[lenze]

Why use a formula? A Pivot Table will work just fine.

lenze

 

[Big Monkey]

Well for one I've never used pivot tables and I have a project due soon. I'm not sure if I have time to learn a new function and change the entire layout of this spreadsheet in a few days.

 

[barry houdini]

Try something like

=SUMPRODUCT(--(A1:A100<>""),--(B1:B100="mytext"))

 

[Big Monkey]

Awesome!

Thanks harry.

Question though...

=SUMPRODUCT(--(C2:C65536),--(I2:I65536=O18))

=SUMPRODUCT(--(C2:C65536<>""),--(I2:I65536=O18))

Why does the first formula sum up the values in the range, and the second formula only counts the non blank cells in that same range?

If I didn't see it work, I would think that the second formula is asking Excel to sum the values in the range if the cell is not blank. I'm glad it works, but I don't understand the logic.

 

[barry houdini]

You need to understand how SUMPRODUCT works. In this formula

=SUMPRODUCT(--(C2:C6<>""),--(I2:I6=O18))

the (C2:C6<>"") part returns an array of TRUE/FALSE values, e.g.

{TRUE;TRUE;TRUE;FALSE}

the -- then "co-erces" this to 1/0 values, e.g.

{1;1;1;0}

SUMPRODUCT then multiplies the arrays and sums them, effectively giving you a count of rows where column I contains O18 and column C is not blank.

If you only have numbers in column C then in this formula

=SUMPRODUCT(--(C2:C65536),--(I2:I65536=O18))

the first -- is unnecessary because you already have numbers in column C so it could be

=SUMPRODUCT(C2:C65536,--(I2:I65536=O18))

To sum column C when column I is O18, although that can be more easily achieved with

=SUMIF(I:I,O18,C:C)

 

[Big Monkey]

OK. This formula below gives me an average I'm looking for and it works perfect

=SUMPRODUCT(C2:C65536,--(I2:I65536=O18))/SUMPRODUCT(--(C2:C65536<>""),--(I2:I65536=O18))

I am using this same formula for 5 different columns(switch column C with D, E, F, and G)

Now I want to get the average for all columns combined, C:G. I tried to change it to

=SUMPRODUCT(C2:G65536,--(I2:I65536=O18))/SUMPRODUCT(--(C2:G65536<>""),--(I2:I65536=O18))

but it gives me an error?

I tried to use the trace option to see if I can find out where the mistake was and it froze my PC

Help? =]

 

[barry houdini]

Try

=AVERAGE(IF(I2:I65536=O18,IF(C2:G65536,C2:G65536)))

confirmed with CTRL+SHIFT+ENTER

edit: amended formula

note: you can do it the way you're attempting, although I don't recommend it, but you need to change the syntax slightly

=SUMPRODUCT((C2:G65536)*(I2:I65536=O18))/SUMPRODUCT((C2:G65536<>"")*(I2:I65536=O18))

 

[Big Monkey]

LOL

Yet another different solution to get the job done.

**** houdini, you really are magic

 

[Sothonnop]

Try something like

=SUMPRODUCT(--(A1:A100<>""),--(B1:B100="mytext"))

You saved my time of research