Re: [ExcelVBA] formula not working

 

Hello Pam

First, this is correct :- =IF(MONTH(J5)=7,"yes","no") 

Next - =SUMIF(J:J,MONTH(7),L:L) 
I take this to mean that you are wanting to sum all cells in column L where the corresponding row in column J  evaluates to Month 7.

This requires an array formula, (ugh) :-

{ =SUM(IF(MONTH(J:J)=7,L:L,0)) }

Type in the above formula without the curly braces and then instead of Enter, do Control + Shift + Enter.

If the formula in the formula bar then has the curly braces you have got it right.

You did not say how you calculated the days worked in column L  Without more information it currently looks like you are summing all work that starts in month 7 and finishes in any later month ? 

Regards 

Derek Turner
Office Automation Consultant
England
+++

>________________________________
> From: dguillett1 <dguillett1@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Tuesday, 16 October 2012, 14:45
>Subject: Re: [ExcelVBA] formula not working
>
>
> 
>k1 has 7/1/2012
>
>=IF(OR($I3>=EDATE(K$1,1),$J3<K$1),0,IF(AND(YEAR($J3)=YEAR(K$1),MONTH($J3)=MONTH(K$1)),DAY($J3), DATE(YEAR(K$1),MONTH(K$1)+1,1)-DATE(YEAR(K$1),MONTH(K$1),1))-IF(AND(YEAR($I3)=YEAR(K$1),MONTH($I3)=MONTH(K$1)),DAY($I3), 0))
>
>copied down to get count for July
>
>Don Guillett
>Microsoft Excel Developer
>SalesAid Software
>dguillett1@gmail.com
>
>From: Pam
>Sent: Monday, October 15, 2012 12:25 PM
>To: ExcelVBA@yahoogroups.com
>Subject: [ExcelVBA] formula not working
>
>I am working with dates, trying to sum up the number of days worked in a
>given month.
>I have a column for start date (col J), end date (col k), and a total column
>(col L).
>
>I want to sum the days worked by month in a different column.
>
>I used this formula.
>
>=SUMIF(J:J,MONTH(7),L:L)
>
>The formula's answer was 0.
>
>The correct answer should be 20.
>
>In trying to troubleshoot my formula I did this (The content of J5 is
>7/23/12):
>
>=IF(J5=MONTH(7),"yes","no") I get NO. The correct answer is YES.
>
>If I type =MONTH(J5) I get an answer of 7. That is the correct answer.
>
>What am I doing wrong?
>
>Pam
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (33)

Recent Activity:

• New Members 7

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___