Excel 2007 : Counting cells in one row for every third column

[Propnistasaga]

Hi all,

Please take a look at the image attached below.
I'd like to create the following:

in C5 I need a formula which counts how many times there's an "x" in every 'Pres' column in row 5. So in this image, it needs to check F5, I5, L5 and O5.
I tried this:

Please Login or Register  to view this content.

but this only checks 'till I5. I even tried nesting offset-functions but that didn't turn out well either...
The formula has to check EACH third column so I can add more Pres/Abs/Exc columns at the end without adapting this total formula..
My guess is it has to be written in a recursive way..

Anyone has any ideas how to do this?

Thanks in advance!

[NBVC]

How about:

=Countifs($3:$3,"Pres",5:5,"x")

or in pre2007

=Sumproduct(--($C$3:$Z$3="Pres"),--($C5:$Z5="x"))

adjust ranges to suit.. cannot use whole rows with Sumproduct in pre2007

[Propnistasaga]

How about:

=Countifs($3:$3,"Pres",5:5,"x")

Thanks for your quick reply!

Your formula is working when put into another row, but when inserted in the same row as the formula searches in, it gives a loop error.
I don't have any clue how to adjust the $3:$3 part so it starts searching from F5 instead of A5.

EDIT: I rewrote so row 3 is locked when dragging the formula:

Please Login or Register  to view this content.

[NBVC]

Thanks for your quick reply!

I don't have any clue how to adjust the $3:$3 part so it starts searching from F5 instead of A5.

just change the $3:$3 to a defined range... e.g. $F$5:$O$5

The $ freezes the columns/rows so that you can drag down the formula and always (absolutely) reference the same range...

[Propnistasaga]

just change the $3:$3 to a defined range... e.g. $F$5:$O$5

The $ freezes the columns/rows so that you can drag down the formula and always (absolutely) reference the same range...

Yes that's true, but my question was how to define a range starting from F5 and then taking the rest of the row..
Anyway, I found a workaround (adding an extra column in between, setting the range including that column and then hide it)
Thanks for helping me on this!