Excel help

A[L]C

A[L]C

A[L]C

Soldato
Joined
18 Oct 2002
Posts
7,514
Location
Maidenhead


Hi all,

On the above screenshot, I would like to do a count of how many saturdays and sundays (row 1 value S) have a 'h' in row 3.

ie above it would show 1

Any ideas?



edit: I can do this: =COUNTIF(M3,"H")+COUNTIF(N3,"H")+COUNTIF(T3,"H")+COUNTIF(U3,"H")+COUNTIF(AA3,"H")*COUNTIF(AB3,"H")+COUNTIF(AH3,"H")+COUNTIF(AI3,"H")+COUNTIF(AO3,"H")

but would prefer something that looks for an S and counts the h in the same column
 
Last edited:
do an =IF(AND formula to look for 2 sets of criteria. Or, tbh, you can just double up on the formula you've already got, but that's getting really long.

=COUNTIF(M4:AH4,"H")+COUNTIF(M4:AH4,"S")

will count all the S and H from left to right, assuming there will be no S or H in other columns. If there are, you'd need to do each S and H column seperately, which will mean a loooong formula. I'm not sure of another way of doing it outside of the =If(And.
 
Last edited:
Ok take a deep breath...

This is the solution I came up with, and it's messy to say the least, but it works fine from what I can tell.

Spreadsheet


Formula
=IF(ISERROR(IF(HLOOKUP("S",B$1:B3,MATCH($A7,$A$1:$A$3,0),FALSE)="H",1,0)=TRUE),0,(IF(HLOOKUP("S",B$1:B3,MATCH($A7,$A$1:$A$3,0),FALSE)="H",1,0)))

Column "W" is just a sum formula.
 
Do you have to have the answer as a formula or do you just want the answer?

If you put that in a pivot table it'll give you the number straight away. If you just want a number on the sheet then put the pivot table on a second tab and link to the row which comes back with the answer for S.
 
Andy100 said:
Do you have to have the answer as a formula or do you just want the answer?

If you put that in a pivot table it'll give you the number straight away. If you just want a number on the sheet then put the pivot table on a second tab and link to the row which comes back with the answer for S.
Click to expand...
OOh I dont know anything about pivot tables tbh!

Namsnik said:
Hope this solution is not too late!
Click to expand...

Thanks a lot, I will try that one out, even just to see how it works!

Sorry for the late reply, been extremely busy. I was using Robbie G's solution which seems to work great, will have a look at the others for future reference
 
Robbie, I tested my solution as posted. The -- in the formulae coerces the true/false results to be interpreted as 1 (=true) or 0 (= false) and so can be used as I suggested.
If you use an "*" then you don't need the --

=SUMPRODUCT(--(C3:U3="S"),--(C4:U4="H"))
will give the same result as
=SUMPRODUCT((C3:U3="S")*(C4:U4="H"))

Just depends which you find clearer.

Regards,
 
If you've never used pivot tables then you're missing out on one of the most powerful functions of Excel.

I could not live without them for data analysis.

For your data set you'd end up with something like this:

ALCData.jpg
 
You must log in or register to reply here.
Back
Top Bottom