I have a large excel spreadsheet where I want the average number of days an issue was opened to be calculated based upon the type of issue. I have a formula that calculates the count of the types accurately in one column, now I need to have a count on another column based upon the value in the first column.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Conditional Formula
- Thread starter tps131
- Start date
AnotherHiggins
Technical User
Have a look at the SUMIF function.
To figure the average, you can combine that with the results of a Countif function.
[tab][COLOR=blue white]=SumIf(A1:A1000, "Thing1", B1:B1000) / CountIf(A1:A1000,"Thing1")[/color]
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
To figure the average, you can combine that with the results of a Countif function.
[tab][COLOR=blue white]=SumIf(A1:A1000, "Thing1", B1:B1000) / CountIf(A1:A1000,"Thing1")[/color]
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
- Thread starter
- #3
AnotherHiggins
Technical User
Glad to help!
![[cheers]](/data/assets/smilies/cheers.gif "[cheers] [cheers]")
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
- Thread starter
- #5
Another issue has also surfaced. What would be the easiest way in an excel formula to combine two criteria. Example, now within the overall group(1st criteria "C") want to cut that down by (5)days(2nd criteria "G")...This is what I think it would look like but it isnt working. Any suggestions??
=Sum(Countif(C:C, "Group Modify") & (G:G, "<5"))
=Sum(Countif(C:C, "Group Modify") & (G:G, "<5"))
- Status
