Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF FORMULAS
USING THIS INFORMATION DIRECTLY BELOW..
If the weight of a Bag was under 49lbs it would be considered UNDER. If the weight of a Bag was 49lbs to 50.99 it would be considered GOOD. If the weight of a Bag was 51lbs or over it would be considered OVER. My formula to find the number of saltbags that are under weight is =COUNTIF(B4:B18,"<49") My formula to find the number of saltbags that are over weight is =COUNTIF(B4:B18,"=51") HOW WOULD I WRITE THIS COUNTIF FORMULA???? What formula would i use to find the number of salt bags that are "Good" weight? how would i write 49 but <51?? ALSO.. then how would i find total weight of bags that are Under and Over weight? i know i would use =SUMIF(B5:B18, ... but then i dont know what i would write for the criteria or sum_range? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF FORMULAS
My preference for figuring a range w/ COUNTIF or SUMIF is to calculate two
totals and subtract. So the result for 49 to 50.99 is =countif(b4:b18,"=49")-countif(b4:b18,"=51). That it, the number between 49 and 60 is all those at least 49 less all those at least 51. As for the sumif, I think of the three arguments of the function as where to look, what to look for, what to add when finding it. So in this case, the total weight of the Under would be =sumif(b4:b18,"<49",b4:b18). "Alexis" wrote: USING THIS INFORMATION DIRECTLY BELOW.. If the weight of a Bag was under 49lbs it would be considered UNDER. If the weight of a Bag was 49lbs to 50.99 it would be considered GOOD. If the weight of a Bag was 51lbs or over it would be considered OVER. My formula to find the number of saltbags that are under weight is =COUNTIF(B4:B18,"<49") My formula to find the number of saltbags that are over weight is =COUNTIF(B4:B18,"=51") HOW WOULD I WRITE THIS COUNTIF FORMULA???? What formula would i use to find the number of salt bags that are "Good" weight? how would i write 49 but <51?? ALSO.. then how would i find total weight of bags that are Under and Over weight? i know i would use =SUMIF(B5:B18, ... but then i dont know what i would write for the criteria or sum_range? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF FORMULAS
For combo count: =SUMPRODUCT(--(B4:B18=49),--(B4:B18<51)) And to sum Over and Under: =SUMIF(B4:B18,"<=49")+SUMIF(B4:B18,"51") And to sum Within Weight: =SUMPRODUCT(--(B4:B18=49),--(B4:B18<51),B4:B18) I have assumed you wanted to include 49 lbs in the calculations. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=494709 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Use CountIF with conditional formulas | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |