Countcells if they match certain criteria from other cells

[FormulaQuestioner]

I have been playing around with the countif statements and am trying to
figure out a forumla which I can not see to code:

Criteria

Count the cells from d6:d7 if cell has the word "PS 2" and (e6:e7 or f6:f7
does not say "Not on Network") *I think that this could just be the same
formula for the one where it checks for blank, but if I am wrong I added this*

Any help would be appreciated

For the real table the ranges are much larger, I just put them as small
numbers for less typing.

 

[Pete_UK]

Use COUNTIF when you only have one criteria. Where you have more, then
use SUMPRODUCT, like this:

=SUMPRODUCT((D610="PS 2")*(E6:E10<>"Not on Network"))

I've made the ranges a bit bigger - they should be equal in size, but
cannot be a full column (unless you have XL2007).

Hope this helps.

Pete

 

[FormulaQuestioner]

Thank-you

Use COUNTIF when you only have one criteria. Where you have more, then
use SUMPRODUCT, like this:

=SUMPRODUCT((D610="PS 2")*(E6:E10<>"Not on Network"))

I've made the ranges a bit bigger - they should be equal in size, but
cannot be a full column (unless you have XL2007).

Hope this helps.

Pete

 

[FormulaQuestioner]

How would I change it where D610 could be "Ps 2" or "USB" and E6:E10 could
not be "Not on Network" would I put the code:

I tried the code

=SUMPRODUCT((D610="PS 2")*(D610="USB")*(E6:E10<>"Not on Network"))

And it always results in a zero. I know it is because D can not have PS 2
and USB in the same cell at the same time. How could I change it to be either
PS2 or USB?

 

[JP]

The + sign acts as the logical 'OR' function. So the formula would be

=SUMPRODUCT(((D610="PS 2")+(D610="USB"))*(E6:E10<>"Not on
Network"))

i.e. "PS 2" or "USB" in D610, and "Not on Network" in E6:E10.

HTH,
JP