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

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

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

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

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

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