|
COUNTIF and SUMIF Functions
I have a spreadsheet set up as follows: Col A has Dates Col B has production units Col C has Energy Units Etc One row for every day of the year starting at row 2, through row 366. What I want to do is sum the production and energy units for every day that the production was greater than 5% of the average annual production. I have tried =COUNTIF($B$2:$B$366,AVERAGE($B$2:$B$366)*.05) This results in error messages. I tried using a seperate cell for the average, E.g. cell B367 =AVERAGE($B$2:$B$366)*.05 Then using the wizard =COUNTIF($B$2:$B$366,B367) changes to =COUNTIF($B$2:$B$366,"B367") and the reference is not used. What am I doing wrong? -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
COUNTIF and SUMIF Functions
hi DC,
try =sumproduct(--(B2:b366average(b2:b366)*5%)) hth regards from Brazil Marcelo "DCSwearingen" escreveu: I have a spreadsheet set up as follows: Col A has Dates Col B has production units Col C has Energy Units Etc One row for every day of the year starting at row 2, through row 366. What I want to do is sum the production and energy units for every day that the production was greater than 5% of the average annual production. I have tried =COUNTIF($B$2:$B$366,AVERAGE($B$2:$B$366)*.05) This results in error messages. I tried using a seperate cell for the average, E.g. cell B367 =AVERAGE($B$2:$B$366)*.05 Then using the wizard =COUNTIF($B$2:$B$366,B367) changes to =COUNTIF($B$2:$B$366,"B367") and the reference is not used. What am I doing wrong? -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
COUNTIF and SUMIF Functions
=sumproduct(--($b$2:$b$366average($b$2:$b$366)*.05),$b$2:$b$366 ) -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
COUNTIF and SUMIF Functions
answering your own questions?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DCSwearingen" wrote in message ... =sumproduct(--($b$2:$b$366average($b$2:$b$366)*.05),$b$2:$b$366 ) -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
COUNTIF and SUMIF Functions
Yes, I guess I did answer my own question. Almost as soon as I posted I saw several other questions regarding COUNT and COUNTIF, after reading them, I tried the SUMPRODUCT route. Had to go to a couple of MVP references to see how the double minus sign impacted the formula. Excel Help does not make that distinction anywhere I could find. My understanding is that basically the double minus sign, turns that portion of the function into a more flexible IF function since it returns a 1 for TRUE, and then the manipulation can be made for the true tests. Thank you for taking the time to read and respond to our issues... -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
COUNTIF and SUMIF Functions
No, you are right, Help doesn't mention it. As you say the double unary
changes True to 1, False to 0, and it is then possible to multiply that array of values by the array of 1/0 values that represent met/failed conditions, and hence the result. There is more detail at http://www.xldynamic.com/source/xld.SUMPRODUCT.html .. I bet you learnt more by working it out for yourself <g -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DCSwearingen" wrote in message news:DCSwearingen.2auh35_1152728107.8493@excelforu m-nospam.com... Yes, I guess I did answer my own question. Almost as soon as I posted I saw several other questions regarding COUNT and COUNTIF, after reading them, I tried the SUMPRODUCT route. Had to go to a couple of MVP references to see how the double minus sign impacted the formula. Excel Help does not make that distinction anywhere I could find. My understanding is that basically the double minus sign, turns that portion of the function into a more flexible IF function since it returns a 1 for TRUE, and then the manipulation can be made for the true tests. Thank you for taking the time to read and respond to our issues... -- DCSwearingen Getting old, but love computers. ------------------------------------------------------------------------ DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506 View this thread: http://www.excelforum.com/showthread...hreadid=560762 |
| All times are GMT +1. The time now is 04:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com