real simple Excel question 2

[fumei]

I will immediately admit I am embarrassed that my Excel skill is so poor I have to ask...what can I say? I am an Excel moron. I have been trying to figure this out, but the shear volume of possible formulae is making me bonkers.

I have (simplified):

B C
yadda yes
yadda no
yadda yes
yadda yes
blah no
blah no
whatever yes
whatever no
whatever no

OK, I know how to filter things on screen so I can see it, to get

# of "yes" for yadda - 2

But I have to actually look at it and count. Surely (but I am so dumb I can not find it yet) there must be a way to put a formula in a cell to get this. I can use COUNTIF to get the number of yadda (4). I can get the number of "yes" in C (4). But how do I get that of those 4, 2 are yadda?

Gerry

 

[SkipVought]

Gerry,

Here's an answer from a Word dummy...
[tt]
2003
=sumproduct(--(Column B Range="Yadda")*(Column C Range="Yes"))

2007
=COUNTIFS(Column B Range,"Yadda",Column C Range,"Yes")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluedragon2]

SUMPRODUCT formula.

=SUMPRUDUCT((A1:A10="yadda")*(B1:B10="yes"))

FAQ68-4725 would explain it...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Skip, We are getting ready to use 2007 at work. I have only used it once briefly, are their a lot of formula changes in it?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I am really liking the new features, especially as they relate to Tables and references.

The formula changes I have been using are the COUNTIF[red]S[/red] and SUMIF[red]S[/red].

Sort can have more than 3 levels.

Conditional Formatting can have more than 3 criteria.

Seems daunting initially, as the look and feel is significantly changed. This has helped me

http://office.microsoft.com/en-us/products/FX102774021033.aspx

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Hoohoo! Thank you both. Hmmm, should I give both a star? I must admit I am happy.

=SUMPRODUCT((D2228="Technology Services Organization")*(K2:K228="yes")*(L2:L228="yes"))

Extrapolating and adding another factor, I am now able to easily state that of the 122 people in Technology Services Organization, and of the 46 people signed up for AIX training (K), 17 of them have taken on-line training for greater than 1 hour (L).

Hoohoo! This Excel thing has some uses.......

Gerry

 

[Lilliabeth]

There is also AVERAGEIFS

another good new one is IFERROR; it is simpler than =IF(ERROR(yadda...



--Lilliabeth

 

[fumei]

That went way over my head.

Gerry

 

[Lilliabeth]

LOL, It might help if I had written it correctly. IFERROR replaces IF(ISERROR(.

They allow you to specify a result that should be displayed if the formula would otherwise return an error.

For example:
=IF(ISERROR(AVERAGE(A1:A500)),"No Data",AVERAGE(A1:A500))
will return the text No Data if the average would return an error (which it would if A1:A500 are all empty cells)

Simpler is
=IFERROR(AVERAGE(A1:A500),"NoData")

--Lilliabeth

 

[SkipVought]

Here's one I can relate to: =wdIFDUMMY(wdNOSTYLES,"WORD DUMMY")


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

In what context? WHAT is equaling wdIFDUMMY?


Oh



Never mind. I get it. Skip, Skip, Skip you are Texan, style is inherent.

OK, a question.

If I put:

=SUMPRODUCT((D2228="Technology Services Organization")*(K2:K228="yes")

into (whatever), say, F240.

Why can I not use:

=SUMPRODUCT(F240*(L2:K228="yes"))

It does work if I tack on L2:l228 to the whole thing:

=SUMPRODUCT((D2228="Technology Services Organization")*(K2:K228="yes")*(L2:L228="yes"))

but not if I reference the formula result in F240.

Gerry

 

[SkipVought]

cuz each of the (expressions) is an TRUE/FALSE array, not just a value.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Oh.



Doh. That makes sense.

Gerry

 

[fumei]

How would I change:

=SUMPRODUCT((D2228="Technology Services Organization")*(K2:K228="yes")*(L2:L228="yes"))

to use D2228 on a different sheet? Say a sheet named "SUMMARY". I have been trying to understand the use of !

Gerry

 

[SkipVought]

Gerry,

You can do references without typing anything; simply point 'n' click.

For instance you TYPE...
[tt]
=SUMPRODUCT((
[/tt]
then select the Summary sheet tab and select the appropriate RANGE on that sheet, using your mouse and shift key, resulting in...
[tt]
=SUMPRODUCT((Summary!D2228
[/tt]
then continue to enter the operator and literal, etc.

But I prefer to use Named Ranges, one of which would reference Summary!D2228, and F3 would display ALL the Named Ranges available on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Cool. I am finding that this Excel thing actually has some interesting aspects.

Gerry

 

[SkipVought]

one or two, maybe. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluedragon2]

Thanks Skip and Lilliabeth. I am looking forward to start using it.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...