| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting and sumproduct formulas
I am attempting to count non-blank cells in one column that have to mee criteria from another column. I have played with the sumproduc function but to no avail....can someone assist? Example following: Column A represents the Month and Year and Column B represents Cost incurred. I want to count the number of times a Cost was incurre during the specific Month/Year. When a cost is not incurred, the cel remains blank. Column A Column B 01/2004 01/2004 $25.00 01/2004 02/2004 $15.00 02/2004 $20.00 02/2004 If the formula works correctly, my results would show 01/2004 1 02/2004 2 Please let me know if you have any ideas! :) Thanks............. -- samiam ----------------------------------------------------------------------- samiam1's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=48433 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting and sumproduct formulas
Why not take a look at the sub-total function from the Data Menu - it allows a summary by your date(key) field and a sum, count etc for the data -- Cheers Nigel "samiam1" wrote in message ... I am attempting to count non-blank cells in one column that have to meet criteria from another column. I have played with the sumproduct function but to no avail....can someone assist? Example following: Column A represents the Month and Year and Column B represents Costs incurred. I want to count the number of times a Cost was incurred during the specific Month/Year. When a cost is not incurred, the cell remains blank. Column A Column B 01/2004 01/2004 $25.00 01/2004 02/2004 $15.00 02/2004 $20.00 02/2004 If the formula works correctly, my results would show 01/2004 1 02/2004 2 Please let me know if you have any ideas! :) Thanks.............. -- samiam1 ------------------------------------------------------------------------ samiam1's Profile: http://www.excelforum.com/member.php...o&userid=28741 View this thread: http://www.excelforum.com/showthread...hreadid=484338 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting and sumproduct formulas
=sumproduct(-(Month(A1:A30)=1),-(Year(A1:A30)=2004),--(B1:B30<"")
-- Regards, Tom Ogilvy "samiam1" wrote in message ... I am attempting to count non-blank cells in one column that have to meet criteria from another column. I have played with the sumproduct function but to no avail....can someone assist? Example following: Column A represents the Month and Year and Column B represents Costs incurred. I want to count the number of times a Cost was incurred during the specific Month/Year. When a cost is not incurred, the cell remains blank. Column A Column B 01/2004 01/2004 $25.00 01/2004 02/2004 $15.00 02/2004 $20.00 02/2004 If the formula works correctly, my results would show 01/2004 1 02/2004 2 Please let me know if you have any ideas! :) Thanks.............. -- samiam1 ------------------------------------------------------------------------ samiam1's Profile: http://www.excelforum.com/member.php...o&userid=28741 View this thread: http://www.excelforum.com/showthread...hreadid=484338 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting and sumproduct formulas
I am attempting to have this tally as new information is entered-foregoing having to manually sort & subtotal. I may be forced to do so manually, but wanted to explore all options prior to submitting to manual work. -- samiam1 ------------------------------------------------------------------------ samiam1's Profile: http://www.excelforum.com/member.php...o&userid=28741 View this thread: http://www.excelforum.com/showthread...hreadid=484338 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting and sumproduct formulas
I appreciate all of your help! I was able to figure it out.....Thank again Samiam -- samiam ----------------------------------------------------------------------- samiam1's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=48433 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting and sumproduct formulas
Is the Month and year true dates or just text? If dates use
=SUMPRODUCT(--(A1:A100-DAY(A1:A100)+1=--"2004-01-01"),--(NOT(ISBLANK(B1:B100 ))),B1:B100) if it is text then use =SUMPRODUCT(--(A1:A100="01/2004"),--(NOT(ISBLANK(B1:B100))),B1:B100) -- HTH RP (remove nothere from the email address if mailing direct) "samiam1" wrote in message ... I am attempting to count non-blank cells in one column that have to meet criteria from another column. I have played with the sumproduct function but to no avail....can someone assist? Example following: Column A represents the Month and Year and Column B represents Costs incurred. I want to count the number of times a Cost was incurred during the specific Month/Year. When a cost is not incurred, the cell remains blank. Column A Column B 01/2004 01/2004 $25.00 01/2004 02/2004 $15.00 02/2004 $20.00 02/2004 If the formula works correctly, my results would show 01/2004 1 02/2004 2 Please let me know if you have any ideas! :) Thanks.............. -- samiam1 ------------------------------------------------------------------------ samiam1's Profile: http://www.excelforum.com/member.php...o&userid=28741 View this thread: http://www.excelforum.com/showthread...hreadid=484338 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
| counting multiple criteria using SUMPRODUCT and getting #VALUE err | Excel Discussion (Misc queries) | |||
| counting dates using SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Adding a counting function within SumProduct | Excel Worksheet Functions | |||
| Counting non blanks with SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Linear Mode
