Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
Hi,
I think I understand so try this in march.xls =SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0)) Enter with Ctrl+Shift+Enter It will divide each cell in QTD.xls by march.xls b1 and count to number of time the result is = 20%. Change 0.2 to 0.3 for 30% Mike "RoadKill" wrote: Okay, I am a total rookie with SumIFs. I want workbook March.xls to link whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
Okay, let me clarify. It sounds like this is close. If cell B1 in March
equals "George" then I want to count the number of Georges in Column A of QTD that have a score of 20-29.99% in Column B of QTD as well as the number of scores above 30%. Sorry for the confusion. "Mike H" wrote: Hi, I think I understand so try this in march.xls =SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0)) Enter with Ctrl+Shift+Enter It will divide each cell in QTD.xls by march.xls b1 and count to number of time the result is = 20%. Change 0.2 to 0.3 for 30% Mike "RoadKill" wrote: Okay, I am a total rookie with SumIFs. I want workbook March.xls to link whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
Thinking it over, let's simplify it to a single workbook.
When the contents of H1 (George) matches any instances of it in column A1:A100, I want it to count the number of scores of 20-29.99% in B1:B100. "RoadKill" wrote: Okay, let me clarify. It sounds like this is close. If cell B1 in March equals "George" then I want to count the number of Georges in Column A of QTD that have a score of 20-29.99% in Column B of QTD as well as the number of scores above 30%. Sorry for the confusion. "Mike H" wrote: Hi, I think I understand so try this in march.xls =SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0)) Enter with Ctrl+Shift+Enter It will divide each cell in QTD.xls by march.xls b1 and count to number of time the result is = 20%. Change 0.2 to 0.3 for 30% Mike "RoadKill" wrote: Okay, I am a total rookie with SumIFs. I want workbook March.xls to link whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF
You have more than one criteria, so you can't use SUMIF - try this
instead: =SUMPRODUCT((B$1:B$100=0.2)*(B$1:B$100<0.3)*(A$1: A$100="George")) Or, if you put George in cell D1 (and other names below): =SUMPRODUCT((B$1:B$100=0.2)*(B$1:B$100<0.3)*(A$1: A$100=D1)) and copy down to cover your names in column D. Hope this helps. Pete On Mar 14, 9:53*pm, RoadKill wrote: Thinking it over, let's simplify it to a single workbook. When the contents of H1 (George) matches any instances of it in column A1:A100, I want it to count the number of scores of 20-29.99% in B1:B100. "RoadKill" wrote: Okay, let me clarify. It sounds like this is close. If cell B1 in March equals "George" then I want to count the number of Georges in Column A of QTD that have a score of 20-29.99% in Column B of QTD as well as the number of scores above 30%. Sorry for the confusion. "Mike H" wrote: Hi, I think I understand so try this in march.xls =SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0)) Enter with Ctrl+Shift+Enter It will divide each cell in QTD.xls by march.xls b1 and count to number of time the result is = 20%. Change 0.2 to 0.3 for 30% Mike "RoadKill" wrote: Okay, I am a total rookie with SumIFs. I want workbook March.xls to link whatever is in cell B1 of March.xls to link to QTD.xls workbook column A1:A100 and count the number of instances that are over 20% as well as those over 30%. Does this make sense? Thanks- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
Sumif? | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |