software

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

for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)

maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;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:

x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)

drag it all the way down the side of my data

and then do something like:

countif(z:z,quot;fooYesquot;)

It's a pain in the arse, I tell you whot. Please help me out.
=SUMPRODUCT(--(X2:X500=quot;fooquot;),--(Y2:Y500lt;100))

will count quot;fooquot; in A where Y is leas than 100

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Undrlinequot; gt; wrote in message
...
gt; So, here's something along the lines of what I'm trying to do:
gt;
gt; for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)
gt;
gt; maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;100))
gt; or maybe it's a whole other function.
gt;
gt; I keep having to create extra go-between columns that concatenate two
gt; columns, and then work from that:
gt;
gt; x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)
gt;
gt; drag it all the way down the side of my data
gt;
gt; and then do something like:
gt;
gt; countif(z:z,quot;fooYesquot;)
gt;
gt; It's a pain in the arse, I tell you whot. Please help me out.
gt;
gt;
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

quot;Peo Sjoblomquot; wrote:

gt; =SUMPRODUCT(--(X2:X500=quot;fooquot;),--(Y2:Y500lt;100))
gt;
gt; will count quot;fooquot; in A where Y is leas than 100
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Undrlinequot; gt; wrote in message
gt; ...
gt; gt; So, here's something along the lines of what I'm trying to do:
gt; gt;
gt; gt; for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)
gt; gt;
gt; gt; maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;100))
gt; gt; or maybe it's a whole other function.
gt; gt;
gt; gt; I keep having to create extra go-between columns that concatenate two
gt; gt; columns, and then work from that:
gt; gt;
gt; gt; x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)
gt; gt;
gt; gt; drag it all the way down the side of my data
gt; gt;
gt; gt; and then do something like:
gt; gt;
gt; gt; countif(z:z,quot;fooYesquot;)
gt; gt;
gt; gt; It's a pain in the arse, I tell you whot. Please help me out.
gt; gt;
gt; gt;
gt;
gt;
gt;

This is a good place to start:

xldynamic.com/source/xld.SUMPRODUCT.htmlquot;Undrlinequot; wrote:

gt; k, works, saves me a bit of time, but my question is why? That's not at all
gt; how the description of sumproduct sounds like it works. It sounds more like
gt; (sum*sum) in the description. Perhaps it has to do with those double dashes?
gt; What do the double-dashes mean?
gt;
gt; Thx,
gt; Jndrline
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(X2:X500=quot;fooquot;),--(Y2:Y500lt;100))
gt; gt;
gt; gt; will count quot;fooquot; in A where Y is leas than 100
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; nwexcelsolutions.com
gt; gt;
gt; gt;
gt; gt; quot;Undrlinequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; So, here's something along the lines of what I'm trying to do:
gt; gt; gt;
gt; gt; gt; for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)
gt; gt; gt;
gt; gt; gt; maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;100))
gt; gt; gt; or maybe it's a whole other function.
gt; gt; gt;
gt; gt; gt; I keep having to create extra go-between columns that concatenate two
gt; gt; gt; columns, and then work from that:
gt; gt; gt;
gt; gt; gt; x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)
gt; gt; gt;
gt; gt; gt; drag it all the way down the side of my data
gt; gt; gt;
gt; gt; gt; and then do something like:
gt; gt; gt;
gt; gt; gt; countif(z:z,quot;fooYesquot;)
gt; gt; gt;
gt; gt; gt; It's a pain in the arse, I tell you whot. Please help me out.
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

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=quot;fooquot;),--(X1:X65535=quot;oofquot;))

yields zero

sumproduct(--(or(X1:X65535=quot;fooquot;,X1:X65535=quot;oofquot;)))

just doesn't work

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

Thanks.

-Jndrline
quot;JMBquot; wrote:

gt; This is a good place to start:
gt;
gt; xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt;
gt; quot;Undrlinequot; wrote:
gt;
gt; gt; k, works, saves me a bit of time, but my question is why? That's not at all
gt; gt; how the description of sumproduct sounds like it works. It sounds more like
gt; gt; (sum*sum) in the description. Perhaps it has to do with those double dashes?
gt; gt; What do the double-dashes mean?
gt; gt;
gt; gt; Thx,
gt; gt; Jndrline
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(X2:X500=quot;fooquot;),--(Y2:Y500lt;100))
gt; gt; gt;
gt; gt; gt; will count quot;fooquot; in A where Y is leas than 100
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt;
gt; gt; gt; Peo Sjoblom
gt; gt; gt;
gt; gt; gt; nwexcelsolutions.com
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Undrlinequot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; So, here's something along the lines of what I'm trying to do:
gt; gt; gt; gt;
gt; gt; gt; gt; for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)
gt; gt; gt; gt;
gt; gt; gt; gt; maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;100))
gt; gt; gt; gt; or maybe it's a whole other function.
gt; gt; gt; gt;
gt; gt; gt; gt; I keep having to create extra go-between columns that concatenate two
gt; gt; gt; gt; columns, and then work from that:
gt; gt; gt; gt;
gt; gt; gt; gt; x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)
gt; gt; gt; gt;
gt; gt; gt; gt; drag it all the way down the side of my data
gt; gt; gt; gt;
gt; gt; gt; gt; and then do something like:
gt; gt; gt; gt;
gt; gt; gt; gt; countif(z:z,quot;fooYesquot;)
gt; gt; gt; gt;
gt; gt; gt; gt; It's a pain in the arse, I tell you whot. Please help me out.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

It's not a problem, it's because you ask it to be both quot;fooquot; and quot;oofquot;
The formula I gave you equals AND, if you want OR and count both quot;fooquot; and
quot;oofquot; you
can use

=SUMPRODUCT(--((X2:X500=quot;fooquot;) (X2:X500=quot;oofquot;)gt;0),--(Y2:Y500lt;100))

or just those 2

=SUMPRODUCT(--((X2:X500=quot;fooquot;) (X2:X500=quot;oofquot;)gt;0))--

Regards,

Peo Sjoblom

Nothwest Excel Solutions

www.nwexcelsolutions.com

remove ^^ from email
Undrline wrote:
gt; read it, it helped me understand the double-dash as a unary operator,
gt; what it does, and how it works. In practice, I came up with a
gt; problem though:
gt;
gt; One gets a zero result if one wants to use a condition that re-uses
gt; the same array:
gt;
gt; sumproduct(--(X1:X65535=quot;fooquot;),--(X1:X65535=quot;oofquot;))
gt;
gt; yields zero
gt;
gt; sumproduct(--(or(X1:X65535=quot;fooquot;,X1:X65535=quot;oofquot;)))
gt;
gt; just doesn't work
gt;
gt; how would you do this, and still be able to use more than two
gt; conditionals?
gt;
gt; Thanks.
gt;
gt; -Jndrline
gt;
gt;
gt;
gt; quot;JMBquot; wrote:
gt;
gt;gt; This is a good place to start:
gt;gt;
gt;gt; xldynamic.com/source/xld.SUMPRODUCT.html
gt;gt;
gt;gt;
gt;gt; quot;Undrlinequot; wrote:
gt;gt;
gt;gt;gt; k, works, saves me a bit of time, but my question is why? That's
gt;gt;gt; not at all how the description of sumproduct sounds like it works.
gt;gt;gt; It sounds more like (sum*sum) in the description. Perhaps it has
gt;gt;gt; to do with those double dashes? What do the double-dashes mean?
gt;gt;gt;
gt;gt;gt; Thx,
gt;gt;gt; Jndrline
gt;gt;gt;
gt;gt;gt; quot;Peo Sjoblomquot; wrote:
gt;gt;gt;
gt;gt;gt;gt; =SUMPRODUCT(--(X2:X500=quot;fooquot;),--(Y2:Y500lt;100))
gt;gt;gt;gt;
gt;gt;gt;gt; will count quot;fooquot; in A where Y is leas than 100
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt;
gt;gt;gt;gt; Regards,
gt;gt;gt;gt;
gt;gt;gt;gt; Peo Sjoblom
gt;gt;gt;gt;
gt;gt;gt;gt; nwexcelsolutions.com
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; quot;Undrlinequot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; So, here's something along the lines of what I'm trying to do:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; for every row where column x says quot;fooquot; countif(y:y,quot;lt;100quot;)
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; maybe it's something like countif(and(x:x=quot;fooquot;,y:ylt;100))
gt;gt;gt;gt;gt; or maybe it's a whole other function.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; I keep having to create extra go-between columns that concatenate
gt;gt;gt;gt;gt; two columns, and then work from that:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; x1amp;if(y1lt;100,quot;Yesquot;,quot;Noquot;)
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; drag it all the way down the side of my data
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; and then do something like:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; countif(z:z,quot;fooYesquot;)
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; It's a pain in the arse, 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=quot;fooquot;) (X1:X65535=quot;oofquot;))

quot;Undrlinequot; wrote:

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

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

=SUMPRODUCT(--((X2:X500=quot;fooquot;) (X2:X500=quot;oofquot;)gt;0),--(Y2:Y500lt;100))

Most wonderful.

-Jndrline
quot;Peo Sjobomquot; wrote:

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