formula won't work

[johngio]

hi all,

my formula won't do what I want it to. I want it to sum values in a column based on two conditions:
1.the dates are within range
2. the $B$41 number is equal to the number in the D column

Here is my formula:

=SUM(IF(AND(YEAR($G$29:$G$300)=YEAR(Sheet1!$F$7),MONTH($G$29:$G$300)=MONTH('Sheet1'!$F$7),DAY('Sheet1'!$F$7)>=DAY($G$29:$G$300)>DAY(0)), IF('Sheet1'!$B$41=$D$29:$D$300, $AJ$29:$AJ$300, 0),"0"))

It keeps returning me a value of zero, when i should be getting a number

Any ideas ?

Thanks

 

[johngio]

also, if I enter the final number in the formula (the output if the main if statement is not true) as any number, it spits that out as the result; i.e - no summing seems to be occurring.

this is the part I'm talking about:

, $AJ$29:$AJ$300, 0),"5"))

(last section of the formula)

Thanks

 

[daniels012]

I have not tried it but, when I have a formula that long, I break down each part into separate cells then add, multiply, subtract accordingly.
Try that and see if it helps. Many times you find out why it's not working that way.

Hope this Helps,
Michael

 

[johngio]

thanks

tried that

seems to work fine for one cell, but doesn't work over range

 

[shades]

Is that an array formula? Try clicking in cell (F2), then CTRL + SHIFT + ENTER

Summing on two conditions is often better done using SUMPRODUCT.

 

[Brian from Maui]

hi all,

my formula won't do what I want it to. I want it to sum values in a column based on two conditions:
1.the dates are within range
2. the $B$41 number is equal to the number in the D column

Here is my formula:

=SUM(IF(AND(YEAR($G$29:$G$300)=YEAR(Sheet1!$F$7),MONTH($G$29:$G$300)=MONTH('Sheet1'!$F$7),DAY('Sheet1'!$F$7)>=DAY($G$29:$G$300)>DAY(0)), IF('Sheet1'!$B$41=$D$29:$D$300, $AJ$29:$AJ$300, 0),"0"))

It keeps returning me a value of zero, when i should be getting a number

Any ideas ?

Thanks

What does this part suppose to do?

DAY('Sheet1'!$F$7)>=DAY($G$29:$G$300)>DAY(0))

 

[johngio]

that checks if the day part of the date is within a given range (eg - between the 14th and the 1st of january)

 

[Brian from Maui]

that checks if the day part of the date is within a given range (eg - between the 14th and the 1st of january)

Try,

=SUMPRODUCT(--(DateRange>=A1),--(DateRange<=A2),--(DRange=A3),RangeToSum)

Where A1 and A2 house dates of interest and A3 is the criteria to match. Change the ranges to suit your needs. All ranges must be the same size and no whole column references. Enter the formula with just Enter.