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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

=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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.

Is there a function Countif that operates like Sumif?
 

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" wrote in message
...
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 arse, I tell you whot. Please help me out.