Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf Function Question | Excel Discussion (Misc queries) | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
sumif function with a difference | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
How to combine a vlookup with a sumif function!!! | Excel Discussion (Misc queries) |