Array Formula Question
 

I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B)
The syntax of SUMPRODUCT is SUMPRODUCT ( array-1, array-2, ...)
You are making arrays so SP works, Countif does not
any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"IPerlovsky" wrote in message
...
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

I am trying to count and max in this array: M2:M2000 based on 2 criterion:
J2:J2000=E8 and K2:K2000=E9. Any ideas?
--
iperlovsky


"Bernard Liengme" wrote:

The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B)
The syntax of SUMPRODUCT is SUMPRODUCT ( array-1, array-2, ...)
You are making arrays so SP works, Countif does not
any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"IPerlovsky" wrote in message
...
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

Does:

=SUMPRODUCT(MAX((J2:J200=E8)*(K2:K200=E9)*(M2:M200 )))

Do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"IPerlovsky" wrote in message
...
I am trying to count and max in this array: M2:M2000 based on 2 criterion:
J2:J2000=E8 and K2:K2000=E9. Any ideas?
--
iperlovsky


"Bernard Liengme" wrote:

The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B)
The syntax of SUMPRODUCT is SUMPRODUCT ( array-1, array-2, ...)
You are making arrays so SP works, Countif does not
any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"IPerlovsky" wrote in message
...
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)

That one is getting a conditional sum

For a conditional count:

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9))

For the conditional max:

Array entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000))

Biff

"IPerlovsky" wrote in message
...
I am trying to count and max in this array: M2:M2000 based on 2 criterion:
J2:J2000=E8 and K2:K2000=E9. Any ideas?
--
iperlovsky


"Bernard Liengme" wrote:

The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B)
The syntax of SUMPRODUCT is SUMPRODUCT ( array-1, array-2, ...)
You are making arrays so SP works, Countif does not
any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"IPerlovsky" wrote in message
...
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

I never heard The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B) ???

The correct syntax for COUNTIF
COUNTIF(range-A,criteria)

The correct syntax for SUMIF
SUMIF(range-A, criteria, range-B)



"Bernard Liengme" wrote:

The syntax of COUNTIF is COUNTIF(range-A, criteria, range-B)
The syntax of SUMPRODUCT is SUMPRODUCT ( array-1, array-2, ...)
You are making arrays so SP works, Countif does not
any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"IPerlovsky" wrote in message
...
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky

Array Formula Question
 

Condition Count

=COUNT(IF(J2:J2000=E8,IF(K2:K2000=E9,M2:M2000)))
=COUNT(IF(J2:J2000=E8,IF(K2:K2000=E9,1)))
=SUM(IF(J2:J2000=E8,IF(K2:K2000=E9,1)))

Ctrl+Shift+Enter

Condition Max

=MAX(IF(J2:J2000=E8),IF(K2:K2000=E9,M2:M2000)))

Ctrl+Shift+Enter

On Mar 10, 4:42 am, IPerlovsky wrote:
I am receiving "the formula you typed contains an error" when I try to
execute the following formulas. Any ideas why these don't work?

=COUNTIF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)
=MAX(IF((--(J2:J2000=E8),--(K2:K2000=E9)),M2:M2000)))

when this one does...

=SUMPRODUCT(--(J2:J2000=E8),--(K2:K2000=E9),M2:M2000)
--
iperlovsky