| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array, Formula, Constant, IF Statement Question???? | Excel Discussion (Misc queries) | |||
| Array Question | Excel Discussion (Misc queries) | |||
| Formula/Array question with dates | Excel Worksheet Functions | |||
| Multicell Array Formula and List Question | Excel Worksheet Functions | |||
| SUM array formula question | Excel Worksheet Functions | |||
Linear Mode
