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.
Not sure I understood the part about the F column but try this:
=SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy")*OR(F1:F10="yyy")*(G1:G10<>"zzz"))
HTH
JG
"telewats" wrote:
>
> 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: http://www.excelforum.com/member.php...o&userid=30270
> View this thread: http://www.excelforum.com/showthread...hreadid=503018
>
>
Try:
=SUMPRODUCT((B1:B30="xxx")*((F1:F30="yyy1")+(F1:F30="yyy2")+(F1:F30="yyy3"))*(G1:G30="zzz"))
Although I'm sure that there are more efficient way of doing it.
--
HTH
Sandy
[email protected]
[email protected] with @tiscali.co.uk
"telewats" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...o&userid=30270
> View this thread: http://www.excelforum.com/showthread...hreadid=503018
>
=SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,yyy",F17:F20="yyy"))*(G17:G20<>"zzz"))
seems to work for me
--
Greetings from New Zealand
Bill K
"pinmaster" <[email protected]> wrote in message
news:[email protected]...
> Not sure I understood the part about the F column but try this:
>
> =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy")*OR(F1:F10="yyy")*(G1:G10<>"zzz"))
>
> HTH
> JG
>
> "telewats" wrote:
>
>>
>> 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:
>> http://www.excelforum.com/member.php...o&userid=30270
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=503018
>>
>>
"Bill Kuunders" <[email protected]> wrote in message
news:%[email protected]...
> =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,yyy",F17:F20="yyy"))*(G17:G20<>"zzz"))
>
> 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 & 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 "+" 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="xxx")*((F1:F30="yyy1")+(F1:F30="yyy2")+(F1:F30="yyy3"))*(G1:G30<>"zzz"))
--
HTH
Sandy
[email protected]
[email protected] with @tiscali.co.uk
"Bill Kuunders" <[email protected]> wrote in message
news:%[email protected]...
> =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,yyy",F17:F20="yyy"))*(G17:G20<>"zzz"))
>
> seems to work for me
>
> --
> Greetings from New Zealand
> Bill K
> "pinmaster" <[email protected]> wrote in message
> news:[email protected]...
>> Not sure I understood the part about the F column but try this:
>>
>> =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy")*OR(F1:F10="yyy")*(G1:G10<>"zzz"))
>>
>> HTH
>> JG
>>
>> "telewats" wrote:
>>
>>>
>>> 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:
>>> http://www.excelforum.com/member.php...o&userid=30270
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=503018
>>>
>>>
>
>
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
"Sandy Mann" wrote:
> Try:
>
> =SUMPRODUCT((B1:B30="xxx")*((F1:F30="yyy1")+(F1:F30="yyy2")+(F1:F30="yyy3"))*(G1:G30="zzz"))
>
> Although I'm sure that there are more efficient way of doing it.
>
> --
> HTH
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "telewats" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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:
> > http://www.excelforum.com/member.php...o&userid=30270
> > View this thread: http://www.excelforum.com/showthread...hreadid=503018
> >
>
>
>
"pinmaster" <[email protected]> wrote in message
news:[email protected]...
>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.
I'm still waiting for Aladin Akyurek to happen by and say that it is
inefficient <g>
--
Regards
Sandy
[email protected]
[email protected] with @tiscali.co.uk
Inefficient?? I don't know about that, seems an obvious solution to me!
Regards
JG
"Sandy Mann" wrote:
> "pinmaster" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
> I'm still waiting for Aladin Akyurek to happen by and say that it is
> inefficient <g>
>
> --
> Regards
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
>
>
>
Thank You
I didn't check it all because I wasn't expecting any problems.
again I'm learning every day............
Bill K
"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> "Bill Kuunders" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,yyy",F17:F20="yyy"))*(G17:G20<>"zzz"))
>>
>> 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 & 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 "+" 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="xxx")*((F1:F30="yyy1")+(F1:F30="yyy2")+(F1:F30="yyy3"))*(G1:G30<>"zzz"))
>
>
> --
> HTH
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "Bill Kuunders" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,yyy",F17:F20="yyy"))*(G17:G20<>"zzz"))
>>
>> seems to work for me
>>
>> --
>> Greetings from New Zealand
>> Bill K
>> "pinmaster" <[email protected]> wrote in message
>> news:[email protected]...
>>> Not sure I understood the part about the F column but try this:
>>>
>>> =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy")*OR(F1:F10="yyy")*(G1:G10<>"zzz"))
>>>
>>> HTH
>>> JG
>>>
>>> "telewats" wrote:
>>>
>>>>
>>>> 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:
>>>> http://www.excelforum.com/member.php...o&userid=30270
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=503018
>>>>
>>>>
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks