Count IF with 3 types of criteria...

[LaurenHancy]

Hello,

Can you please help me with the following:

I need to show the sum of all cells in ('ALL CLOSED'!$W$2:$W$10000)

But with the following search criteria:

('ALL CLOSED'!$E$2:$E$10000=B$2)
(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11)

Many thanks in advance.

Lx

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--('ALL CLOSED'!$E$2:$E$10000=B$2),--(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11"),'ALL CLOSED'!$W$2:$W$10000)

 

[VoG]

Hi. Try

=SUMPRODUCT(--('ALL CLOSED'!$E$2:$E$10000=B$2),--(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11"),'ALL CLOSED'!$W$2:$W$10000)

 

[Aladin Akyurek]

Hello,

Can you please help me with the following:

I need to show the sum of all cells in ('ALL CLOSED'!$W$2:$W$10000)

But with the following search criteria:

('ALL CLOSED'!$E$2:$E$10000=B$2)
(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11)

Many thanks in advance.

Lx

Control+shift+enter, not just enter:

=SUM(
    IF(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11"
    IF('ALL CLOSED'!$E$2:$E$10000=B$2,
      'ALL CLOSED'!$W$2:$W$10000)))

 

[LaurenHancy]

thanks guys!