Which Formula

[kmaki]

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

 

[Barb Reinhardt]

Try something like this

=COUNTIF(B1:B10,"x")

I'm not sure why you tell use that you have multiple sheets though.

 

[Don Guillett]

Look at the help index for SUMIF and then combine

 

[kmaki]

How can I attach an image of my file?

Look at the help index for SUMIF and then combine

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[kmaki]

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.

 

[Pete_UK]

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

 

[kmaki]

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]

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

 

[Domenic]

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!