Using Countif with And

[Mike]

I am trying to count E15:E659 that contains "VM" if the corresponding row
under H is greater than 0.

 

[Pete_UK]

You can only use COUNTIF with a single condition. Use this instead:

=SUMPRODUCT((ISNUMBER(SEARCH("VM",E15:E659)))*(H15:H659>0))

You can't use wildcards here, hence the need for the ISNUMBER/SEARCH
combination. Note also that you can't use full-column references with
SP (unless you have Excel 2007).

Hope this helps.

Pete

 

[Mike]

Works great Thanks! I was trying:

=COUNT(IF($E$15:$E$659="VM", $H15:$H659>0))

but it always just gave me a value of 1.

 

[Pete_UK]

Thanks for feeding back, Mike.

Pete

 

[Ajay]

Thanks for feeding back, Mike.

Pete





- Show quoted text -

Use this array formula : {=SUM(IF($E$15:$E$659=J14,IF($H$15:$H
$659>K14,1,0),0))}
file exiting from cell where you will be using this formula, use Ctrl
+ Shift + Enter

 

[Ajay]

Use this array formula : {=SUM(IF($E$15:$E$659=J14,IF($H$15:$H
$659>K14,1,0),0))}
file exiting from cell where you will be using this formula, use Ctrl
+ Shift + Enter- Hide quoted text -

- Show quoted text -

Please use this formula instead of which i posted earlier: {=SUM(IF
($E$15:$E$659="VM",IF($H$15:$H$659>0,1,0),0))}