Is there a function Countif that operates like Sumif?

So, here's something along the lines of what I'm trying to do:

for every row where column x says "foo" countif(y:y,"<100")

maybe it's something like countif(and(x:x="foo",y:y<100))
or maybe it's a whole other function.

I keep having to create extra go-between columns that concatenate two
columns, and then work from that:

x1&if(y1<100,"Yes","No")

drag it all the way down the side of my data

and then do something like:

countif(z:z,"fooYes")

It's a pain in the ****, I tell you whot. Please help me out.

=SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))

will count "foo" in A where Y is leas than 100

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Undrline" <[email protected]> wrote in message
news:[email protected]...
> So, here's something along the lines of what I'm trying to do:
>
> for every row where column x says "foo" countif(y:y,"<100")
>
> maybe it's something like countif(and(x:x="foo",y:y<100))
> or maybe it's a whole other function.
>
> I keep having to create extra go-between columns that concatenate two
> columns, and then work from that:
>
> x1&if(y1<100,"Yes","No")
>
> drag it all the way down the side of my data
>
> and then do something like:
>
> countif(z:z,"fooYes")
>
> It's a pain in the ****, I tell you whot. Please help me out.
>
>

k, works, saves me a bit of time, but my question is why? That's not at all
how the description of sumproduct sounds like it works. It sounds more like
(sum*sum) in the description. Perhaps it has to do with those double dashes?
What do the double-dashes mean?

Thx,
Jndrline

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
>
> will count "foo" in A where Y is leas than 100
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Undrline" <[email protected]> wrote in message
> news:[email protected]...
> > So, here's something along the lines of what I'm trying to do:
> >
> > for every row where column x says "foo" countif(y:y,"<100")
> >
> > maybe it's something like countif(and(x:x="foo",y:y<100))
> > or maybe it's a whole other function.
> >
> > I keep having to create extra go-between columns that concatenate two
> > columns, and then work from that:
> >
> > x1&if(y1<100,"Yes","No")
> >
> > drag it all the way down the side of my data
> >
> > and then do something like:
> >
> > countif(z:z,"fooYes")
> >
> > It's a pain in the ****, I tell you whot. Please help me out.
> >
> >
>
>
>

This is a good place to start:

http://xldynamic.com/source/xld.SUMPRODUCT.html


"Undrline" wrote:

> k, works, saves me a bit of time, but my question is why? That's not at all
> how the description of sumproduct sounds like it works. It sounds more like
> (sum*sum) in the description. Perhaps it has to do with those double dashes?
> What do the double-dashes mean?
>
> Thx,
> Jndrline
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
> >
> > will count "foo" in A where Y is leas than 100
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > http://nwexcelsolutions.com
> >
> >
> > "Undrline" <[email protected]> wrote in message
> > news:[email protected]...
> > > So, here's something along the lines of what I'm trying to do:
> > >
> > > for every row where column x says "foo" countif(y:y,"<100")
> > >
> > > maybe it's something like countif(and(x:x="foo",y:y<100))
> > > or maybe it's a whole other function.
> > >
> > > I keep having to create extra go-between columns that concatenate two
> > > columns, and then work from that:
> > >
> > > x1&if(y1<100,"Yes","No")
> > >
> > > drag it all the way down the side of my data
> > >
> > > and then do something like:
> > >
> > > countif(z:z,"fooYes")
> > >
> > > It's a pain in the ****, I tell you whot. Please help me out.
> > >
> > >
> >
> >
> >

read it, it helped me understand the double-dash as a unary operator, what it
does, and how it works. In practice, I came up with a problem though:

One gets a zero result if one wants to use a condition that re-uses the same
array:

sumproduct(--(X1:X65535="foo"),--(X1:X65535="oof"))

yields zero

sumproduct(--(or(X1:X65535="foo",X1:X65535="oof")))

just doesn't work

how would you do this, and still be able to use more than two conditionals?

Thanks.

-Jndrline



"JMB" wrote:

> This is a good place to start:
>
> http://xldynamic.com/source/xld.SUMPRODUCT.html
>
>
> "Undrline" wrote:
>
> > k, works, saves me a bit of time, but my question is why? That's not at all
> > how the description of sumproduct sounds like it works. It sounds more like
> > (sum*sum) in the description. Perhaps it has to do with those double dashes?
> > What do the double-dashes mean?
> >
> > Thx,
> > Jndrline
> >
> > "Peo Sjoblom" wrote:
> >
> > > =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
> > >
> > > will count "foo" in A where Y is leas than 100
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > http://nwexcelsolutions.com
> > >
> > >
> > > "Undrline" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > So, here's something along the lines of what I'm trying to do:
> > > >
> > > > for every row where column x says "foo" countif(y:y,"<100")
> > > >
> > > > maybe it's something like countif(and(x:x="foo",y:y<100))
> > > > or maybe it's a whole other function.
> > > >
> > > > I keep having to create extra go-between columns that concatenate two
> > > > columns, and then work from that:
> > > >
> > > > x1&if(y1<100,"Yes","No")
> > > >
> > > > drag it all the way down the side of my data
> > > >
> > > > and then do something like:
> > > >
> > > > countif(z:z,"fooYes")
> > > >
> > > > It's a pain in the ****, I tell you whot. Please help me out.
> > > >
> > > >
> > >
> > >
> > >

It's not a problem, it's because you ask it to be both "foo" and "oof"
The formula I gave you equals AND, if you want OR and count both "foo" and
"oof" you
can use

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100))

or just those 2

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0))


--

Regards,

Peo Sjoblom

Nothwest Excel Solutions

www.nwexcelsolutions.com

remove ^^ from email



Undrline wrote:
> read it, it helped me understand the double-dash as a unary operator,
> what it does, and how it works. In practice, I came up with a
> problem though:
>
> One gets a zero result if one wants to use a condition that re-uses
> the same array:
>
> sumproduct(--(X1:X65535="foo"),--(X1:X65535="oof"))
>
> yields zero
>
> sumproduct(--(or(X1:X65535="foo",X1:X65535="oof")))
>
> just doesn't work
>
> how would you do this, and still be able to use more than two
> conditionals?
>
> Thanks.
>
> -Jndrline
>
>
>
> "JMB" wrote:
>
>> This is a good place to start:
>>
>> http://xldynamic.com/source/xld.SUMPRODUCT.html
>>
>>
>> "Undrline" wrote:
>>
>>> k, works, saves me a bit of time, but my question is why? That's
>>> not at all how the description of sumproduct sounds like it works.
>>> It sounds more like (sum*sum) in the description. Perhaps it has
>>> to do with those double dashes? What do the double-dashes mean?
>>>
>>> Thx,
>>> Jndrline
>>>
>>> "Peo Sjoblom" wrote:
>>>
>>>> =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
>>>>
>>>> will count "foo" in A where Y is leas than 100
>>>>
>>>> --
>>>>
>>>> Regards,
>>>>
>>>> Peo Sjoblom
>>>>
>>>> http://nwexcelsolutions.com
>>>>
>>>>
>>>> "Undrline" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> So, here's something along the lines of what I'm trying to do:
>>>>>
>>>>> for every row where column x says "foo" countif(y:y,"<100")
>>>>>
>>>>> maybe it's something like countif(and(x:x="foo",y:y<100))
>>>>> or maybe it's a whole other function.
>>>>>
>>>>> I keep having to create extra go-between columns that concatenate
>>>>> two columns, and then work from that:
>>>>>
>>>>> x1&if(y1<100,"Yes","No")
>>>>>
>>>>> drag it all the way down the side of my data
>>>>>
>>>>> and then do something like:
>>>>>
>>>>> countif(z:z,"fooYes")
>>>>>
>>>>> It's a pain in the ****, I tell you whot. Please help me out.

I don't think AND/OR functions work well in array formulas like this (I've
not had much success, but that doesn't mean it can't be done)

=SUMPRODUCT((X1:X65535="foo")+(X1:X65535="oof"))

"Undrline" wrote:

> read it, it helped me understand the double-dash as a unary operator, what it
> does, and how it works. In practice, I came up with a problem though:
>
> One gets a zero result if one wants to use a condition that re-uses the same
> array:
>
> sumproduct(--(X1:X65535="foo"),--(X1:X65535="oof"))
>
> yields zero
>
> sumproduct(--(or(X1:X65535="foo",X1:X65535="oof")))
>
> just doesn't work
>
> how would you do this, and still be able to use more than two conditionals?
>
> Thanks.
>
> -Jndrline
>
>
>
> "JMB" wrote:
>
> > This is a good place to start:
> >
> > http://xldynamic.com/source/xld.SUMPRODUCT.html
> >
> >
> > "Undrline" wrote:
> >
> > > k, works, saves me a bit of time, but my question is why? That's not at all
> > > how the description of sumproduct sounds like it works. It sounds more like
> > > (sum*sum) in the description. Perhaps it has to do with those double dashes?
> > > What do the double-dashes mean?
> > >
> > > Thx,
> > > Jndrline
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
> > > >
> > > > will count "foo" in A where Y is leas than 100
> > > >
> > > > --
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > http://nwexcelsolutions.com
> > > >
> > > >
> > > > "Undrline" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > So, here's something along the lines of what I'm trying to do:
> > > > >
> > > > > for every row where column x says "foo" countif(y:y,"<100")
> > > > >
> > > > > maybe it's something like countif(and(x:x="foo",y:y<100))
> > > > > or maybe it's a whole other function.
> > > > >
> > > > > I keep having to create extra go-between columns that concatenate two
> > > > > columns, and then work from that:
> > > > >
> > > > > x1&if(y1<100,"Yes","No")
> > > > >
> > > > > drag it all the way down the side of my data
> > > > >
> > > > > and then do something like:
> > > > >
> > > > > countif(z:z,"fooYes")
> > > > >
> > > > > It's a pain in the ****, I tell you whot. Please help me out.
> > > > >
> > > > >
> > > >
> > > >
> > > >

Awesome. Thank you both for helping, and for keeping on my topic with
subsequent questions.

=SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100))

Most wonderful.

-Jndrline



"Peo Sjobom" wrote:

> It's not a problem, it's because you ask it to be both "foo" and "oof"
> The formula I gave you equals AND, if you want OR and count both "foo" and
> "oof" you
> can use
>
> =SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0),--(Y2:Y500<100))
>
> or just those 2
>
> =SUMPRODUCT(--((X2:X500="foo")+(X2:X500="oof")>0))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Nothwest Excel Solutions
>
> www.nwexcelsolutions.com
>
> remove ^^ from email
>
>
>
> Undrline wrote:
> > read it, it helped me understand the double-dash as a unary operator,
> > what it does, and how it works. In practice, I came up with a
> > problem though:
> >
> > One gets a zero result if one wants to use a condition that re-uses
> > the same array:
> >
> > sumproduct(--(X1:X65535="foo"),--(X1:X65535="oof"))
> >
> > yields zero
> >
> > sumproduct(--(or(X1:X65535="foo",X1:X65535="oof")))
> >
> > just doesn't work
> >
> > how would you do this, and still be able to use more than two
> > conditionals?
> >
> > Thanks.
> >
> > -Jndrline
> >
> >
> >
> > "JMB" wrote:
> >
> >> This is a good place to start:
> >>
> >> http://xldynamic.com/source/xld.SUMPRODUCT.html
> >>
> >>
> >> "Undrline" wrote:
> >>
> >>> k, works, saves me a bit of time, but my question is why? That's
> >>> not at all how the description of sumproduct sounds like it works.
> >>> It sounds more like (sum*sum) in the description. Perhaps it has
> >>> to do with those double dashes? What do the double-dashes mean?
> >>>
> >>> Thx,
> >>> Jndrline
> >>>
> >>> "Peo Sjoblom" wrote:
> >>>
> >>>> =SUMPRODUCT(--(X2:X500="foo"),--(Y2:Y500<100))
> >>>>
> >>>> will count "foo" in A where Y is leas than 100
> >>>>
> >>>> --
> >>>>
> >>>> Regards,
> >>>>
> >>>> Peo Sjoblom
> >>>>
> >>>> http://nwexcelsolutions.com
> >>>>
> >>>>
> >>>> "Undrline" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>>> So, here's something along the lines of what I'm trying to do:
> >>>>>
> >>>>> for every row where column x says "foo" countif(y:y,"<100")
> >>>>>
> >>>>> maybe it's something like countif(and(x:x="foo",y:y<100))
> >>>>> or maybe it's a whole other function.
> >>>>>
> >>>>> I keep having to create extra go-between columns that concatenate
> >>>>> two columns, and then work from that:
> >>>>>
> >>>>> x1&if(y1<100,"Yes","No")
> >>>>>
> >>>>> drag it all the way down the side of my data
> >>>>>
> >>>>> and then do something like:
> >>>>>
> >>>>> countif(z:z,"fooYes")
> >>>>>
> >>>>> It's a pain in the ****, I tell you whot. Please help me out.
>
>
>