MaxIF instead of SumIF
I am doing a SumIF( , , ) and that works fine. Is there a way to do a
MaxIf( , , ). I see in the help that there is no Maxif but what I want to do is return the highest value for that particular occurance in the same data that I am doing the SumIF. Thank you for your help. Steven |
Hi Steven.............
You could do Data Filter AutoFilter............then select your group of interest and sort decending on your column of interest..........that would put the MAX value of that subset at the top of that column............ hth Vaya con Dios, Chuck, CABGx3 "Steven" wrote in message ... I am doing a SumIF( , , ) and that works fine. Is there a way to do a MaxIf( , , ). I see in the help that there is no Maxif but what I want to do is return the highest value for that particular occurance in the same data that I am doing the SumIF. Thank you for your help. Steven |
There is no MAXIF function, but you can use an array
formula. For example, return the maximum value in A1:A100 if B1:B100 = "dog": =MAX(IF(B1:B100="dog",A1:A100)) 2 important notes: 1. This is an array formula, so you must press ctrl/shift/enter, not just enter, after inserting the formula and anytime you edit the cell. XL will place {} around the formula. 2. Array formulas cannot handle entire columns. You cannot use: =MAX(IF(B:B="dog",A:A)) HTH Jason Atlanta, GA -----Original Message----- I am doing a SumIF( , , ) and that works fine. Is there a way to do a MaxIf( , , ). I see in the help that there is no Maxif but what I want to do is return the highest value for that particular occurance in the same data that I am doing the SumIF. Thank you for your help. Steven . |
Thats a good one Jason. Thanks.
|
All times are GMT +1. The time now is 09:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com