# formula won't work

#### johngio

##### Board Regular
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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

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

thanks

tried that

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

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

Summing on two conditions is often better done using SUMPRODUCT.

johngio said:
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))

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

johngio said:
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.

Replies
3
Views
64
Replies
6
Views
218
Replies
7
Views
213
Replies
7
Views
191
Replies
5
Views
207

1,203,674
Messages
6,056,681
Members
444,882
Latest member
cboyce44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back