[LibreOffice] CountIF in range AND in another range

classic Classic list List threaded Threaded
3 messages Options
Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

[LibreOffice] CountIF in range AND in another range

My two ranges:
E2:E455 = IN, OUT
F2:F455 = F or S + Year = "F 2018"

What I want to do is:
countIF E2:E455 = OUT AND F2:F455 = F 2018

After reading the help docs and just cant seem to figure out
the way to write this for two ranges.


--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: CountIF in range AND in another range

At 11:15 03/12/2018 -0600, Wade Smart wrote:
>My two ranges:
>E2:E455 = IN, OUT
>F2:F455 = F or S + Year = "F 2018"
>
>What I want to do is: countIF E2:E455 = OUT AND F2:F455 = F 2018
>
>After reading the help docs and just cant seem to figure out the way
>to write this for two ranges.

How about:
=SUMPRODUCT(E2:E455="OUT";F2:F455="F 2018")

Each of the equations in the arguments creates an array of boolean
values. The SUMPRODUCT() function first multiplies corresponding
members, which requires TRUE to be interpreted as 1 and FALSE as 0.
The product is thus 1 only if both conditions are true. The sum of
all the appropriate 1s is the count you require.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Fwd: [libreoffice-users] CountIF in range AND in another range

In reply to this post by Wade Smart
On Mon, Dec 3, 2018 at 11:45 AM Brian Barker <[hidden email]> wrote:

>
> At 11:15 03/12/2018 -0600, Wade Smart wrote:
> >My two ranges:
> >E2:E455 = IN, OUT
> >F2:F455 = F or S + Year = "F 2018"
> >
> >What I want to do is: countIF E2:E455 = OUT AND F2:F455 = F 2018
> >
> >After reading the help docs and just cant seem to figure out the way
> >to write this for two ranges.
>
> How about:
> =SUMPRODUCT(E2:E455="OUT";F2:F455="F 2018")
>
> Each of the equations in the arguments creates an array of boolean
> values. The SUMPRODUCT() function first multiplies corresponding
> members, which requires TRUE to be interpreted as 1 and FALSE as 0.
> The product is thus 1 only if both conditions are true. The sum of
> all the appropriate 1s is the count you require.
>
> I trust this helps.
>
> Brian Barker

Ah, excellent.
Well, that is a lot easier than what I was trying to do.
Over thinking it.

Thanks Brian.

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy