COUNTIF or WHAT???

  • Thread starter Thread starter Mat Urban
  • Start date Start date
M

Mat Urban

I have two columns and I have a headache!
OK! I need to count how many times A1:A6=2*B1:B6
=IF(A1:A6=2*B1:B6;1;0)
IF function does the job and answeres in Logical_test {0,1,1,0,0,0}!
or {False,True,True,False,Fals,Fals}
What now? How do I count how many times those damn "1" or "true"
occure?
Countif returns #VALUE.

I know, that I could do a seperate column and count row by row and
then sum the "1", but that would also mean that I need to insert
exactly 195 columns in 34 different files! There must be a different
"one cell" formula to count simple multiplication like this.

A B
1 2 4
2 6 3
3 4 2
4 6 4
5 4 5
6 1 1

ans: 2

Thanks very much!
 
Wrap your IF statement in a SUM like so:

=SUM(IF(A1:A6=1*B1:B6;1;0))

and array-enter it.

/i.
 
Or a bit shorter and not array-entered:

=SUMPRODUCT((A1:A6=2*B1:B6)+0)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
immanuel...
Great! I knew that it should be simple, but I never expected to be SO
simple! Works like a charm! Thanks!

I'm sending you a box of Aspirins!

M!
 
Guys...
I knew that my enthusiasm could not last...

I need to count how many times A1:A6=2*B1:B6, but this time cell
values in column A and B also contain letter X (now column is
alfanumeric) which does not help counting! And to make it fun in
column C formula also needs to check if value is "N"!

So in short! How do I count:

A1:A6=2*B1:B6 AND C1:C6="N" in one formula! without those damn "X"

A B C
1 x 4 M
2 6 3 N
3 4 2 N
4 6 4 N
5 4 x L
6 2 1 F ans: 2

Thanks very much!
 
Mat Urban said:
Guys...
I knew that my enthusiasm could not last...

I need to count how many times A1:A6=2*B1:B6, but this time cell
values in column A and B also contain letter X (now column is
alfanumeric) which does not help counting! And to make it fun in
column C formula also needs to check if value is "N"!

So in short! How do I count:

A1:A6=2*B1:B6 AND C1:C6="N" in one formula! without those damn "X"

A B C
1 x 4 M
2 6 3 N
3 4 2 N
4 6 4 N
5 4 x L
6 2 1 F ans: 2

Thanks very much!
Click to expand...

One possibility:
=SUMPRODUCT(--(A1:A6=2*SUBSTITUTE(B1:B6,"X",0)),--(C1:C6="N"))
 
=SUM(IF(ISNUMBER((A1:A6=2*B1:B6)*(C1:C6="N")),(A1:A6=2*B1:B6)*(C1:C6="N")))

which must be confirmed with control+shift+enter instead of just with enter.
 
Both of them work! Perfect! I sometimes wonder how come I never
stumble over theese funny "put together" formulas!

Thanks guys!
 
This is getting frustrating... I know, but please bear with me!

In the following table I need to calculate average of values in second
column that are connected to A and B respectively. So how to
Average (B1:B10) IF (A1:A10="A")

A 6
B 1
B 2 Average A 7,28
A 7 Average B 2,71
B 3
A 6
B 4
B 2
A 8
B 5
A 9
A 9
B 2
A 6
 
One way:

=AVERAGE(IF(A1:A111="A";B1:B111))
.... must be entered by holding down the Ctrl & Shift keys then hit Enter.


--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)
 
I managed to write my own which works :) !, but its array entered. Is
non-array method possible? Rethoric one isn't it!
 
Mat Urban said:
I managed to write my own which works :) !, but its array entered. Is
non-array method possible? Rethoric one isn't it!
Click to expand...

What's wrong with array formulas?

But, yes, you don't need array formulas for conditional averages.

=SUMIF(A1:A10,"A",B1:B10)/COUNTIF(A1:A10,"A")
 
Back
Top