Count occurrances with conditions

[WMO]

I am trying to count in 3 columns. Column A will have entries of "B
or "S" and columns B and C will have "1" or "0".

A B C
B 0 1
B 1 0
B 1 1
S 1 1

I want to count how many times column C has a 1 with a B or S. That i
no problem, I can do that. I also want to count the number of time
column B has a 1 with a B or S. That is no problem, I can do that.
However, the third count I want to count is when a 1 appears in bot
column B and C, I want ONLY to count the occurrance in column C. I CA
NOT figure that out.

These are the formulas I am using for single occurrances.
=SUM(IF(C18:C38="B",IF(R18:R38>0,1,0)))
=SUM(IF(C18:C38="S",IF(R18:R38>0,1,0)))

I enter them with SHIFT/CTRL/Enter

Can anyone help? Thanks!!

 

[Bob Phillips]

=SUMPRODUCT(--(C18:C38="B"),--(R18:R38=1),--(S18:S38=1))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[WMO]

Trying to reach Bob Phillips who logged on as a guest. I appreciat
your response but it did not resolve my issue. I have now attached
portion of the spreadsheet with more detail.

If you (or anyone else) can make an additional response, I would
greatly appreciate it. Thank

+-------------------------------------------------------------------
|Filename: excel forum.zip
|Download: http://www.excelforum.com/attachment.php?postid=3678
+-------------------------------------------------------------------

 

[Bob Phillips]

I didn't sign on to the forum as a guest, I didn't come to the forum at all.
Your forum questions are automatically forwarded to the Microsoft Public
newsgroups which is where I see them.

Anyway, try this alternative

=SUMPRODUCT(--(C17:C37="B"),--(Q17:Q37=1),--(R17:R37<>1))

for bulk items

 

[WMO]

Bob.....thanks very much. That works perfectly. Seems so simple once
see it. I suppose everything is simple when you understand. I ha
tried, at least, 4,000 variations of my knowledge.....nothing worked.
I was getting totally frustrated.

I found the Excel forum site. Had never used it before so was no
aware of how it worked. Did not realize you came from "somewher
else", but am I glad you did!

You have impressed everybody who sits around me. We all had trie
something but it (they) never worked. I need to brush up on SUMPRODUC
and will certainly do that.

Again, thanks VERY MUCH for your help. You, indeed, saved the day fo
us

 

[Bob Phillips]

If you want to brush up on SUMPRODUCT, take a look at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html