Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
Hi all.
I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
You can use sumproduct for counting - basically:
=SUMPRODUCT((condition1)*(condition2)*(condition3) ) or if you prefer: =SUMPRODUCT(--(condition1),--(condition2),--(condition3)) This will give a count of when all three conditions are met (no range to sum) - perhaps you can adapt it to your situation. Hope this helps. Pete On Apr 28, 4:44*pm, SteveDB1 wrote: Hi all. I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
sumproduct will count, eh?
I've always used it to just add all the incidences of a specific set of data, which has numeric values. what do your double hyphen/minus signs reference? "Pete_UK" wrote: You can use sumproduct for counting - basically: =SUMPRODUCT((condition1)*(condition2)*(condition3) ) or if you prefer: =SUMPRODUCT(--(condition1),--(condition2),--(condition3)) This will give a count of when all three conditions are met (no range to sum) - perhaps you can adapt it to your situation. Hope this helps. Pete On Apr 28, 4:44 pm, SteveDB1 wrote: Hi all. I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
the Sumproduct function will give you what you want. look at these examples:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html also, you may want to try a pivot table. see these examples: http://peltiertech.com/Excel/Pivots/pivottables.htm http://www.contextures.com/xlPivot02.html Regards, Ryan-- -- RyGuy "Pete_UK" wrote: You can use sumproduct for counting - basically: =SUMPRODUCT((condition1)*(condition2)*(condition3) ) or if you prefer: =SUMPRODUCT(--(condition1),--(condition2),--(condition3)) This will give a count of when all three conditions are met (no range to sum) - perhaps you can adapt it to your situation. Hope this helps. Pete On Apr 28, 4:44 pm, SteveDB1 wrote: Hi all. I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
Pete...
Thanks. I don't why I didn't look at it like this before. I see where it can count now. The way that I'd done it previously was to have only two criteria, and then sum a range where the criteria matched. The counting application I'd want would have 3 crtieria with no sum range. Thanks for the eye opener... This issue is solved. Best. "Pete_UK" wrote: You can use sumproduct for counting - basically: =SUMPRODUCT((condition1)*(condition2)*(condition3) ) or if you prefer: =SUMPRODUCT(--(condition1),--(condition2),--(condition3)) This will give a count of when all three conditions are met (no range to sum) - perhaps you can adapt it to your situation. Hope this helps. Pete On Apr 28, 4:44 pm, SteveDB1 wrote: Hi all. I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
modifying countifs
Cheers, Steve - glad to be of help.
The double unary minuses convert Trues and Falses to 1's and 0's so they can be added arithmetically. Pete On Apr 28, 5:14*pm, SteveDB1 wrote: Pete... Thanks. I don't why I didn't look at it like this before. I see where it can count now. The way that I'd done it previously was to have only two criteria, and then sum a range where the criteria matched. The counting application I'd want would have 3 crtieria with no sum range. Thanks for the eye opener... This issue is solved. Best. "Pete_UK" wrote: You can use sumproduct for counting - basically: =SUMPRODUCT((condition1)*(condition2)*(condition3) ) or if you prefer: =SUMPRODUCT(--(condition1),--(condition2),--(condition3)) This will give a count of when all three conditions are met (no range to sum) - perhaps you can adapt it to your situation. Hope this helps. Pete On Apr 28, 4:44 pm, SteveDB1 wrote: Hi all. I am in need to of worksheet function modification. I've been using the countifs for a verification process, and it's gotten to the point where it's more frustrating then it is helpful. The main problem is that I need to use one column for my criteria range that does not have values in all cells for that column. Which then means that I need to place some kind of a "place holder" such as a space bar key stroke which then messes up my sumproduct function. I was thinking that I'd like to do something akin to a sumproduct, but make it a countproduct, except of course, it doesn't exist. I say this because the sumproduct allows for blank cells, and the countifs/countif does not. What could I use to bypass the blank cell restriction to still get an accurant count on my function. Generally I'm using the following. =countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3) where crtrng is shorthand for crtieria range, and crt is shorthand for the crtieria. Range 1 would be matching names, range 2 would be matching numeric values, and range 3 would be the column with some blank cells, and others that would not be blanks. Always though range 3 would have 5 digit numbers, if any values existed there. Thank you for your help.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIFS for 2003 | Excel Worksheet Functions | |||
Countifs Fx in 07 how in 03? | Excel Discussion (Misc queries) | |||
countifs | Excel Discussion (Misc queries) | |||
2 COUNTIFS | Excel Discussion (Misc queries) | |||
Multiple countifs | Excel Worksheet Functions |