software

I am trying to determine the total count of cells containing certain
data, and I can't get my formula to work correctly. I need to get the
total number of cells that fit the following criteria: If column B
contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
column G is not equal to zzz, then count the cells. I'm sure this can
be done (right?) but I am not having any luck.--
telewats
------------------------------------------------------------------------
telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
View this thread: www.excelforum.com/showthread...hreadid=503018Not sure I understood the part about the F column but try this:

=SUMPRODUCT((B1:B10=quot;xxxquot;)*OR(F1:F10=quot;yyy,yyy,yyyquot; )*OR(F1:F10=quot;yyyquot;)*(G1:G10lt;gt;quot;zzzquot;))

HTH
JG

quot;telewatsquot; wrote:

gt;
gt; I am trying to determine the total count of cells containing certain
gt; data, and I can't get my formula to work correctly. I need to get the
gt; total number of cells that fit the following criteria: If column B
gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt; be done (right?) but I am not having any luck.
gt;
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=503018
gt;
gt;

Try:

=SUMPRODUCT((B1:B30=quot;xxxquot;)*((F1:F30=quot;yyy1quot;) (F1:F3 0=quot;yyy2quot;) (F1:F30=quot;yyy3quot;))*(G1:G30=quot;zzzquot;))

Although I'm sure that there are more efficient way of doing it.

--
HTH

Sandy
with @tiscali.co.ukquot;telewatsquot; gt; wrote in
message ...
gt;
gt; I am trying to determine the total count of cells containing certain
gt; data, and I can't get my formula to work correctly. I need to get the
gt; total number of cells that fit the following criteria: If column B
gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt; be done (right?) but I am not having any luck.
gt;
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=503018
gt;
=SUMPRODUCT((B17:B20=quot;xxxquot;)*(OR(F17:F20=quot;yyy,yyy,y yyquot;,F17:F20=quot;yyyquot;))*(G17:G20lt;gt;quot;zzzquot;))

seems to work for me

--
Greetings from New Zealand
Bill K
quot;pinmasterquot; gt; wrote in message
...
gt; Not sure I understood the part about the F column but try this:
gt;
gt; =SUMPRODUCT((B1:B10=quot;xxxquot;)*OR(F1:F10=quot;yyy,yyy,yyyquot; )*OR(F1:F10=quot;yyyquot;)*(G1:G10lt;gt;quot;zzzquot;))
gt;
gt; HTH
gt; JG
gt;
gt; quot;telewatsquot; wrote:
gt;
gt;gt;
gt;gt; I am trying to determine the total count of cells containing certain
gt;gt; data, and I can't get my formula to work correctly. I need to get the
gt;gt; total number of cells that fit the following criteria: If column B
gt;gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt;gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt;gt; be done (right?) but I am not having any luck.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; telewats
gt;gt; ------------------------------------------------------------------------
gt;gt; telewats's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=30270
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=503018
gt;gt;
gt;gt;
quot;Bill Kuundersquot; gt; wrote in message
...
gt; =SUMPRODUCT((B17:B20=quot;xxxquot;)*(OR(F17:F20=quot;yyy,yyy,y yyquot;,F17:F20=quot;yyyquot;))*(G17:G20lt;gt;quot;zzzquot;))
gt;
gt; seems to work for me

If it works for you then that is fine but it doesn't work for me. I don't
think that OR works in a SUMPRODUCT function.

When I enter xxx in B17:B20, zzz in G18 amp; G20 and just z in G19 then if I
put yyy in ANY cell in F17:F20 the formula return 2.

In other words the OR is making the yyy apply to all cells in F17:F20

The quot; quot; in the formula I posted works like an OR but I see that I forgot to
eliminate the zzz in column G so it should have been:

SUMPRODUCT((B1:B30=quot;xxxquot;)*((F1:F30=quot;yyy1quot;) (F1:F30 =quot;yyy2quot;) (F1:F30=quot;yyy3quot;))*(G1:G30lt;gt;quot;zzzquot;))--
HTH

Sandy
with @tiscali.co.uk

quot;Bill Kuundersquot; gt; wrote in message
...
gt; =SUMPRODUCT((B17:B20=quot;xxxquot;)*(OR(F17:F20=quot;yyy,yyy,y yyquot;,F17:F20=quot;yyyquot;))*(G17:G20lt;gt;quot;zzzquot;))
gt;
gt; seems to work for me
gt;
gt; --
gt; Greetings from New Zealand
gt; Bill K
gt; quot;pinmasterquot; gt; wrote in message
gt; ...
gt;gt; Not sure I understood the part about the F column but try this:
gt;gt;
gt;gt; =SUMPRODUCT((B1:B10=quot;xxxquot;)*OR(F1:F10=quot;yyy,yyy,yyyquot; )*OR(F1:F10=quot;yyyquot;)*(G1:G10lt;gt;quot;zzzquot;))
gt;gt;
gt;gt; HTH
gt;gt; JG
gt;gt;
gt;gt; quot;telewatsquot; wrote:
gt;gt;
gt;gt;gt;
gt;gt;gt; I am trying to determine the total count of cells containing certain
gt;gt;gt; data, and I can't get my formula to work correctly. I need to get the
gt;gt;gt; total number of cells that fit the following criteria: If column B
gt;gt;gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt;gt;gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt;gt;gt; be done (right?) but I am not having any luck.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; telewats
gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt; telewats's Profile:
gt;gt;gt; www.excelforum.com/member.php...oamp;userid=30270
gt;gt;gt; View this thread:
gt;gt;gt; www.excelforum.com/showthread...hreadid=503018
gt;gt;gt;
gt;gt;gt;
gt;
gt;
I was going to reply saying that my formula didn't work after all but I guess
it should be obvious by now. Glad someone was able to come up with something
that works.
Learned something new in the process so that's good, thanks Sandy.

Regards
JG

quot;Sandy Mannquot; wrote:

gt; Try:
gt;
gt; =SUMPRODUCT((B1:B30=quot;xxxquot;)*((F1:F30=quot;yyy1quot;) (F1:F3 0=quot;yyy2quot;) (F1:F30=quot;yyy3quot;))*(G1:G30=quot;zzzquot;))
gt;
gt; Although I'm sure that there are more efficient way of doing it.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;telewatsquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; I am trying to determine the total count of cells containing certain
gt; gt; data, and I can't get my formula to work correctly. I need to get the
gt; gt; total number of cells that fit the following criteria: If column B
gt; gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt; gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt; gt; be done (right?) but I am not having any luck.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; telewats
gt; gt; ------------------------------------------------------------------------
gt; gt; telewats's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30270
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=503018
gt; gt;
gt;
gt;
gt;

quot;pinmasterquot; gt; wrote in message
...
gt;I was going to reply saying that my formula didn't work after all but I
gt;guess
gt; it should be obvious by now. Glad someone was able to come up with
gt; something
gt; that works.

I'm still waiting for Aladin Akyurek to happen by and say that it is
inefficient lt;ggt;

--
Regards

Sandy
with @tiscali.co.ukInefficient?? I don't know about that, seems an obvious solution to me!

Regards
JG

quot;Sandy Mannquot; wrote:

gt; quot;pinmasterquot; gt; wrote in message
gt; ...
gt; gt;I was going to reply saying that my formula didn't work after all but I
gt; gt;guess
gt; gt; it should be obvious by now. Glad someone was able to come up with
gt; gt; something
gt; gt; that works.
gt;
gt; I'm still waiting for Aladin Akyurek to happen by and say that it is
gt; inefficient lt;ggt;
gt;
gt; --
gt; Regards
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt;
gt;
gt;

Thank You

I didn't check it all because I wasn't expecting any problems.
again I'm learning every day............

Bill K

quot;Sandy Mannquot; gt; wrote in message
...
gt; quot;Bill Kuundersquot; gt; wrote in message
gt; ...
gt;gt; =SUMPRODUCT((B17:B20=quot;xxxquot;)*(OR(F17:F20=quot;yyy,yyy,y yyquot;,F17:F20=quot;yyyquot;))*(G17:G20lt;gt;quot;zzzquot;))
gt;gt;
gt;gt; seems to work for me
gt;
gt; If it works for you then that is fine but it doesn't work for me. I don't
gt; think that OR works in a SUMPRODUCT function.
gt;
gt; When I enter xxx in B17:B20, zzz in G18 amp; G20 and just z in G19 then if I
gt; put yyy in ANY cell in F17:F20 the formula return 2.
gt;
gt; In other words the OR is making the yyy apply to all cells in F17:F20
gt;
gt; The quot; quot; in the formula I posted works like an OR but I see that I forgot
gt; to eliminate the zzz in column G so it should have been:
gt;
gt; SUMPRODUCT((B1:B30=quot;xxxquot;)*((F1:F30=quot;yyy1quot;) (F1:F30 =quot;yyy2quot;) (F1:F30=quot;yyy3quot;))*(G1:G30lt;gt;quot;zzzquot;))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt; quot;Bill Kuundersquot; gt; wrote in message
gt; ...
gt;gt; =SUMPRODUCT((B17:B20=quot;xxxquot;)*(OR(F17:F20=quot;yyy,yyy,y yyquot;,F17:F20=quot;yyyquot;))*(G17:G20lt;gt;quot;zzzquot;))
gt;gt;
gt;gt; seems to work for me
gt;gt;
gt;gt; --
gt;gt; Greetings from New Zealand
gt;gt; Bill K
gt;gt; quot;pinmasterquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Not sure I understood the part about the F column but try this:
gt;gt;gt;
gt;gt;gt; =SUMPRODUCT((B1:B10=quot;xxxquot;)*OR(F1:F10=quot;yyy,yyy,yyyquot; )*OR(F1:F10=quot;yyyquot;)*(G1:G10lt;gt;quot;zzzquot;))
gt;gt;gt;
gt;gt;gt; HTH
gt;gt;gt; JG
gt;gt;gt;
gt;gt;gt; quot;telewatsquot; wrote:
gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; I am trying to determine the total count of cells containing certain
gt;gt;gt;gt; data, and I can't get my formula to work correctly. I need to get the
gt;gt;gt;gt; total number of cells that fit the following criteria: If column B
gt;gt;gt;gt; contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and
gt;gt;gt;gt; column G is not equal to zzz, then count the cells. I'm sure this can
gt;gt;gt;gt; be done (right?) but I am not having any luck.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt; telewats
gt;gt;gt;gt; ------------------------------------------------------------------------
gt;gt;gt;gt; telewats's Profile:
gt;gt;gt;gt; www.excelforum.com/member.php...oamp;userid=30270
gt;gt;gt;gt; View this thread:
gt;gt;gt;gt; www.excelforum.com/showthread...hreadid=503018
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;