Modifying an Array Formula

C

carl

I use the following array formula. I wanted to adapt it to count (vs sum). Is
it possible ??

=SUM(IF((Sheet2!$A$2:$A$20000=Sheet1!$E3)*(Sheet2!$J$2:$J$20000=Sheet1!$D$3);Sheet2!$I$2:$I$20000))

Thank you in advance.
 
B

Bernie Deitrick

Carl,

=SUM((Sheet2!$A$2:$A$20000=Sheet1!$E3)*(Sheet2!$J$2:$J$20000=Sheet1!$D$3))

HTH,
Bernie
MS Excel MVP
 
D

Don Guillett

try this simpler approach which is not array entered
=SUMPRODUCT((F1:F4=E1)*(G1:G4=E2))
or just delete the last parameter
=SUM(IF((Sheet2!$A$2:$A$20000=Sheet1!$E3)*(Sheet2!$J$2:$J$20000=Sheet1!$D$3)
)
 
Top