Simplify formula

**SalamanderSam14** · 01-04-2012, 10:11 AM

Hi i was wondering if anyone can help me shink this formula or make it so that i dont have to add another if statement if i want to increase my table

=IF($D$4=D13,$G$4,0)+IF($D$5=D13,$G$5,0)+IF($D$6=D13,$G$6,0)+IF($D$7=D13,$G$7,0)+IF($D$8=D13,$G$8,0)+IF($D$9=D13,$G$9,0)+IF($D$10=D13,$G$10,0)+IF($D$11=D13,$G$11,0)+IF($D$12=D13,$G$12,0)+IF($D$13=D13,$G$13,0)+IF($D$14=D13,$G$14,0)+IF($D$15=D13,$G$15,0)+IF($D$16=D13,$G$16,0)+IF($D$17=D13,$G$17,0)+IF($D$18=D13,$G$18,0)+IF($D$19=D13,$G$19,0)+IF($D$20=D13,$G$20,0)+IF($D$21=D13,$G$21,0)+IF($D$22=D13,$G$22,0)+IF($D$23=D13,$G$23,0)+IF($D$24=D13,$G$24,0)+IF($D$25=D13,$G$25,0)+IF($D$26=D13,$G$26,0)+IF($D$27=D13,$G$27,0)+IF($D$28=D13,$G$28,0)+IF($D$29=D13,$G$29,0)+IF($D$30=D13,$G$30,0)

Thanks

**Andy Pope** · 01-04-2012, 10:17 AM

=sumproduct((d4:d30=d13)*(g4:g30))

**SalamanderSam14** · 01-04-2012, 10:31 AM

Thanks alot, thats made that alot easier, both of those solutions worked

**OnErrorGoto0** · 01-04-2012, 10:18 AM

I think that is just:
=SUMIF($D$4:$D$30,D13,$G$4:$G$30)

**SalamanderSam14** · 01-04-2012, 12:04 PM

If i wanted to change that formula to compare a range of dates instead of one value how would i go about it. For example get the sum for values entered in january 2011?

**adaws** · 01-04-2012, 12:23 PM

let the cell D13 be the "month & year" you are looking for.... and use the same sumif function, or else in a separate column enter the months Jan 2011 to Dec 2011 and in the criteria field select the appropriate month, so ideally the formula would be =sumif(months range, selected month, sum range)...

**OnErrorGoto0** · 01-04-2012, 12:20 PM

Either something like:
=sumproduct((Year(d4:d30)=2011)*(month(d4:d30)=1)*(g4:g30))

or
=SUMIF($D$4:$D$30,">="&DATE(2011,1,1),$G$4:$G$30)-SUMIF($D$4:$D$30,">="&DATE(2011,2,1),$G$4:$G$30)