How Do I Part 3

[Alan Bernardo]

Here's something simple that I can't figure.

Say I want to look in a cell and if either of two things appear, I want to
count the cell.

Here's the formula, with the touchy part toward the end:

=SUMPRODUCT(--(D1229:H12:H29:M12:M29:Q12:Q20="D1"),--(G12:G29:K12:K291229:T12:T29="FW""G"))/J34

There at the end, the way it goes, the cell is counted only if an "FW" and a
"G" is present. I want it to be an "FW" OR a "G".

Thanks,

Alanb

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--((G12:G29:K12:K291229:T12:T29="FW")+(G12:G29:K12:K291229:T12:T29="G")),--(D1229:H12:H29:M12:M29:Q12:Q29="D1"))

I assume that you had a type in your post, Q12:Q20 should be Q12:Q29
correct?

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[Alan Bernardo]

| Try
|
|
=SUMPRODUCT(--((G12:G29:K12:K291229:T12:T29="FW")+(G12:G29:K12:K291229:T12:T29="G")),--(D1229:H12:H29:M12:M29:Q12:Q29="D1"))
|
| I assume that you had a type in your post, Q12:Q20 should be Q12:Q29
| correct?
|
| --

Yes, I missed typed that. I'll try your suggestion.

Thanks very much.

Alan

 

[Alan Bernardo]

| Try
|
|
=SUMPRODUCT(--((G12:G29:K12:K291229:T12:T29="FW")+(G12:G29:K12:K291229:T12:T29="G")),--(D1229:H12:H29:M12:M29:Q12:Q29="D1"))
|

That did the trick. I wasn't thinking along those lines.

Mucho Thanko.

Alanb

 

[Bob Phillips]

Why not just?

=SUMPRODUCT(--(ISNUMBER(MATCH(G12:T18,{"FW","G"},0))),--(D12:Q18="D1"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Alan Bernardo]

| Why not just?
|
| =SUMPRODUCT(--(ISNUMBER(MATCH(G12:T18,{"FW","G"},0))),--(D12:Q18="D1"))
|
| --
| HTH
|
| Bob Phillips
|
Yes, this looks a lot simpler. I'll have to remember this formula, in case
I need something like this in the future.

Thanks,

Alan

 

[Bob Phillips]

The point I was making was that although you broke the ranges into discrete
chunks, it did not specify the non-contiguous areas, it still mapped the
whole range, so why not just say so?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)