countif(and) problem

[Len Dolby]

Array line C4..AA4 contains 5 repeating blocks mixed data, letters and
numbers, blanks. (repeated some 400 lines)

Need to count numeric total of occurrences IF C4 = "H" AND D4 = "Y", H4 =
"H" AND I9 = "Y", etc
There's other combinations, but just the one answer solves all.
What's the exact syntax for COUNTIF(AND) (or, alternate Count function) when
dealing with text, please ? Tried Googling, and ref books - can't find
anything that works. All I want is a single numeric total of occurrences.
Help appreciated !

 

[Tom Ogilvy]

=Sumproduct((C1:C400 = "H")*(D1400= "Y")*(H1:H400 =>"H")*(I1:I400 = "Y"))

Would be a guess at what you want.

Regards,
Tom Ogilvy

 

[Len Dolby]

Tom - Good to see you again in print ! Hope you are well and happy.

Too much info confused the issue - totals needed row-wise, not column-wise.
However the essential clue I needed is all there - resulted in
=SUMPRODUCT(($C5:$W5="H")*($D5:$X5="Y"))
which works as it ought to - copied/pasted approx 600 times for row
totals.
Thanks for the help !

I've another interest now - Lawn Bowling. I'm the new secretary of our Over
60's division. 80 members, 36 matches, and few "computer-literates", but
members appreciate "results" data nicely presented.
I'm keeping reports as simple as possible .. (unlike the dominoes, in which
I still have an interest !)
Sincerely, Len

 

[Tom Ogilvy]

Len,
Good to see you back in print as well. I did recognize the name as being
familiar, but didn't recall the dominoes association until you mentioned it.
Glad the formula led to a solution.