Need a SUM function with a formula

[James111]

I have a simple speadsheet that takes data from another worksheet and populates individual cells with that data. Cells A3:O3 are dates. The Formula I have counts back -16, -15, 14, etc. days from TODAY() finds a date that matches that criteria in the other worksheet and counts the rows with values in them. Most of them have the value of 1 which returns a correct count, but some have a value of 3 or 2 or some other amount. The last column P is an average formula for the 15 days. My question is: How can I count the rows and sum the values >1 and add to the value that is populated into the master worksheet. This is the formula I'm using for cells A4:O4:

=COUNTIF(Sheet2!A:A,TODAY()-16)

 

[Nogslaw]

It sounds to me like you want the SUMIF function. Check it out in the Help Menu.

My guess is you will want something like this:

=SUMIF(Sheet2!A:A, TODAY()-16, Sheet2!B:B)

 

[James111]

I will try this tomorrow when I get back to work. It seems like this is what I need, I'll check it out. Thanks very much for your help. You guys do a fantastic job helping people in this forum. We all appreciate it.

 

[James111]

OK, I tried it and I didn't get any error messages (except the one noted below) but it returned a boolean value "FALSE" instead of the actual value which should be 8. This is the final formula used:

=COUNTIF(Sheet2!A:A,TODAY()-7)=SUMIF(Sheet2A:A,TODAY()-7,Sheet2K4)

comma after Sheet2?
Sheet1 K4 is the cell where the SUMMED value should end up. I first typed K:4(range not cell)? but I did get a microsoft formula correction error that changed it to K4 is this correct? There is a value in one row of 3 which doesn't seem to SUM, the others return 1 for the number of rows instead of value inside the column:row.

<TABLE style="WIDTH: 38pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=51 border=0 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>3</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=51 height=18 x:num>1</TD></TR></TBODY></TABLE>These are the values in J81:J86 of Sheet2

 

[Nogslaw]

To get a count of dates that match your criteria "TODAY()-16", your original COUNTIF formula is still valid.

=COUNTIF(Sheet2!A:A, TODAY()-16)

To add up the values in column J for dates that match your criteria, use the SUMIF formula:

=SUMIF(Sheet2!A:A, TODAY()-16, Sheet2!J:J)

Sheet2!A:A tells the formula to look in Column A on Sheet2
TODAY()-16 tells the formula to find values in that range that match the value of TODAY()-16
Sheet2!J:J tells the formula to add up the values in this range when the previous criteria matches

 

[James111]

Hey Nogslaw, you did it man, Thanx a ton. As long as I typed in the correct syntax and criteria it worked like a charm. I can now apply this formula to other cells to complete the task. Keep up the good work, and you will be worth your weight in GOLD.