Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
My file is a 13 page document, 2 files per page capturing variou data. In
one column there are 5 rows where the data is either x or no. I am trying to set up a formula that will give me 1) a total of all "x" and "no"; 2 a total of all "x" only; 3) total of all "no" only. One page I have rows 379:383 and 388:392, column F reflects the x or no. Thanks in advance for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Try something like this
=COUNTIF(B1:B10,"x") I'm not sure why you tell use that you have multiple sheets though. -- HTH, Barb Reinhardt "kmaki" wrote: My file is a 13 page document, 2 files per page capturing variou data. In one column there are 5 rows where the data is either x or no. I am trying to set up a formula that will give me 1) a total of all "x" and "no"; 2 a total of all "x" only; 3) total of all "no" only. One page I have rows 379:383 and 388:392, column F reflects the x or no. Thanks in advance for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Look at the help index for SUMIF and then combine
-- Don Guillett Microsoft MVP Excel SalesAid Software "kmaki" wrote in message ... My file is a 13 page document, 2 files per page capturing variou data. In one column there are 5 rows where the data is either x or no. I am trying to set up a formula that will give me 1) a total of all "x" and "no"; 2 a total of all "x" only; 3) total of all "no" only. One page I have rows 379:383 and 388:392, column F reflects the x or no. Thanks in advance for your help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
How can I attach an image of my file?
"Don Guillett" wrote: Look at the help index for SUMIF and then combine -- Don Guillett Microsoft MVP Excel SalesAid Software "kmaki" wrote in message ... My file is a 13 page document, 2 files per page capturing variou data. In one column there are 5 rows where the data is either x or no. I am trying to set up a formula that will give me 1) a total of all "x" and "no"; 2 a total of all "x" only; 3) total of all "no" only. One page I have rows 379:383 and 388:392, column F reflects the x or no. Thanks in advance for your help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
One page looks like this, I have a total of 13 pages, would these formulas be
correct once all rows are included in the formula? formula 1. =sumif((rows8:12,"yes")+(rows8:12,"x")+(rows17:21, "yes")+(rows17:21,"x")) formula 2. = sumif((rows8:12'"yes")+(rows17:21,"yes") formulat 3. = sumif((rows8:12,"x")+(rows12:21,"X") Column Row 8 Yes Row 9 X Row 10 Yes Row 11 Row 12 Row 17 x Row 18 X Row 19 Yes Row 20 Row 21 Thanks. "Don Guillett" wrote: Look at the help index for SUMIF and then combine -- Don Guillett Microsoft MVP Excel SalesAid Software "kmaki" wrote in message ... My file is a 13 page document, 2 files per page capturing variou data. In one column there are 5 rows where the data is either x or no. I am trying to set up a formula that will give me 1) a total of all "x" and "no"; 2 a total of all "x" only; 3) total of all "no" only. One page I have rows 379:383 and 388:392, column F reflects the x or no. Thanks in advance for your help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Assuming that data was in column B:
formula1: =COUNTIF(B8:B12,"<") + COUNTIF(B17:B21,"<") formula2: =COUNTIF(B8:B12,"yes") + COUNTIF(B17:B21,"yes") formula3: =COUNTIF(B8:B12,"x") + COUNTIF(B17:B21,"x") Hope this helps. Pete On Apr 19, 10:46*pm, kmaki wrote: One page looks like this, I have a total of 13 pages, would these formulas be correct once all rows are included in the formula? formula 1. * =sumif((rows8:12,"yes")+(rows8:12,"x")+(rows17:21, "yes")+(rows17:21,"x")) * formula 2. = sumif((rows8:12'"yes")+(rows17:21,"yes") formulat 3. = sumif((rows8:12,"x")+(rows12:21,"X") * * * * * * Column Row 8 * * * * Yes Row 9 * * * * * X Row 10 * * * Yes Row 11 Row 12 Row 17 * * * * x Row 18 * * * * *X Row 19 * * * Yes Row 20 Row 21 Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Tried formula 1, I got an error message that formulat too long and I still
have 5 more sets to enter. Here is what I did. =countif(F8:F12,"x")+ countif(F8:F12,"no") + countif(F17:F21,"x') + countif(F17:F21,"no") + countif(F39:F43,"x') + countif(F39:F43,"no") + countif(F48:F52,"x") + countif(F48:F52,"no") + countif(F70:F74,"x") + countif(F70:F74,"no") + countif(F79:F83,"x") + countif(F79:F83,"no") + countif(F101:F105,"x") + countif(F101:F105,"no") + countif(F110:F114,"x") +countif(F110:F114,"no") + countif(F132:F136,"x") + countif(F132:F136,"no") + countif(F141:F145,"x") + countif(F141:F145,"no") + countif(F163:F167,"x") + countif(F163:F167,"no") + countif(F172:F176,"x") + countif(F194:F198,"x") + countif(F194:F198,"no") + countif(F203:F207,"x") + countif(F203:F207,"no") + countif(F225:F229,"x") +countif(F225:F229,"no") + countif(F234:F238,"x") + countif(F234:F238,"no") + countif(F256:F260,"x") + countif(F256:F260,"no") + countif(F265:F269,"x') + countif(F265:F269,"no") + countif(F287:F291,"x") + countif(F287:F291,"no") + countif(F296:F300,"x") + countif(F296:F300,"no") + countif(F318:F322,"x") + countif(F318:F322,"no") "Pete_UK" wrote: Assuming that data was in column B: formula1: =COUNTIF(B8:B12,"<") + COUNTIF(B17:B21,"<") formula2: =COUNTIF(B8:B12,"yes") + COUNTIF(B17:B21,"yes") formula3: =COUNTIF(B8:B12,"x") + COUNTIF(B17:B21,"x") Hope this helps. Pete On Apr 19, 10:46 pm, kmaki wrote: One page looks like this, I have a total of 13 pages, would these formulas be correct once all rows are included in the formula? formula 1. =sumif((rows8:12,"yes")+(rows8:12,"x")+(rows17:21, "yes")+(rows17:21,"x")) formula 2. = sumif((rows8:12'"yes")+(rows17:21,"yes") formulat 3. = sumif((rows8:12,"x")+(rows12:21,"X") Column Row 8 Yes Row 9 X Row 10 Yes Row 11 Row 12 Row 17 x Row 18 X Row 19 Yes Row 20 Row 21 Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Suppose the formula works up to the terms including F296:F300, and
let's say you have that long formula in cell X1. Then in another cell you can finish it off with: =X1 + countif(F318:F322,"x") + countif(F318:F322,"no") and so on for your other 5 sets of data. Hope this helps. Pete On Apr 20, 2:01*am, kmaki wrote: Tried formula 1, I got an error message that formulat too long and I still have 5 more sets to enter. *Here is what I did. =countif(F8:F12,"x")+ countif(F8:F12,"no") + countif(F17:F21,"x') + countif(F17:F21,"no") + countif(F39:F43,"x') + countif(F39:F43,"no") + countif(F48:F52,"x") + countif(F48:F52,"no") + countif(F70:F74,"x") + countif(F70:F74,"no") + countif(F79:F83,"x") + countif(F79:F83,"no") + countif(F101:F105,"x") + countif(F101:F105,"no") + countif(F110:F114,"x") +countif(F110:F114,"no") + countif(F132:F136,"x") + countif(F132:F136,"no") + countif(F141:F145,"x") + countif(F141:F145,"no") + countif(F163:F167,"x") + countif(F163:F167,"no") + countif(F172:F176,"x") + countif(F194:F198,"x") + countif(F194:F198,"no") + countif(F203:F207,"x") + countif(F203:F207,"no") + countif(F225:F229,"x") +countif(F225:F229,"no") + countif(F234:F238,"x") + countif(F234:F238,"no") + countif(F256:F260,"x") + countif(F256:F260,"no") + countif(F265:F269,"x') + countif(F265:F269,"no") + countif(F287:F291,"x") + countif(F287:F291,"no") + countif(F296:F300,"x") + countif(F296:F300,"no") + countif(F318:F322,"x") + countif(F318:F322,"no") "Pete_UK" wrote: Assuming that data was in column B: formula1: * *=COUNTIF(B8:B12,"<") + COUNTIF(B17:B21,"<") formula2: * *=COUNTIF(B8:B12,"yes") + COUNTIF(B17:B21,"yes") formula3: * *=COUNTIF(B8:B12,"x") + COUNTIF(B17:B21,"x") Hope this helps. Pete On Apr 19, 10:46 pm, kmaki wrote: One page looks like this, I have a total of 13 pages, would these formulas be correct once all rows are included in the formula? formula 1. * =sumif((rows8:12,"yes")+(rows8:12,"x")+(rows17:21, "yes")+(rows17:21,"x")) * formula 2. = sumif((rows8:12'"yes")+(rows17:21,"yes") formulat 3. = sumif((rows8:12,"x")+(rows12:21,"X") * * * * * * Column Row 8 * * * * Yes Row 9 * * * * * X Row 10 * * * Yes Row 11 Row 12 Row 17 * * * * x Row 18 * * * * *X Row 19 * * * Yes Row 20 Row 21 Thanks.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Which Formula
Try...
=SUM(IF(ISNUMBER(MATCH(MOD(ROW(F8:F322)-ROW(F8),31)+1,{1,2,3,4,5,10,11,12 ,13,14},0)),IF(ISNUMBER(MATCH(F8:F322,{"No","X"},0 )),1))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , kmaki wrote: Tried formula 1, I got an error message that formulat too long and I still have 5 more sets to enter. Here is what I did. =countif(F8:F12,"x")+ countif(F8:F12,"no") + countif(F17:F21,"x') + countif(F17:F21,"no") + countif(F39:F43,"x') + countif(F39:F43,"no") + countif(F48:F52,"x") + countif(F48:F52,"no") + countif(F70:F74,"x") + countif(F70:F74,"no") + countif(F79:F83,"x") + countif(F79:F83,"no") + countif(F101:F105,"x") + countif(F101:F105,"no") + countif(F110:F114,"x") +countif(F110:F114,"no") + countif(F132:F136,"x") + countif(F132:F136,"no") + countif(F141:F145,"x") + countif(F141:F145,"no") + countif(F163:F167,"x") + countif(F163:F167,"no") + countif(F172:F176,"x") + countif(F194:F198,"x") + countif(F194:F198,"no") + countif(F203:F207,"x") + countif(F203:F207,"no") + countif(F225:F229,"x") +countif(F225:F229,"no") + countif(F234:F238,"x") + countif(F234:F238,"no") + countif(F256:F260,"x") + countif(F256:F260,"no") + countif(F265:F269,"x') + countif(F265:F269,"no") + countif(F287:F291,"x") + countif(F287:F291,"no") + countif(F296:F300,"x") + countif(F296:F300,"no") + countif(F318:F322,"x") + countif(F318:F322,"no") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|