conditional functions 3

[Guest_imported]

If the result in cell C3 is dependent on both cell A3 being a name'x' and cell B3 being another name 'd', how do you set a formula that will count all the times this happens in a column?

 

[iGrant]

The only way I can think of, by using functions, is to have a third column (which you can hide if necessary) with the formula

=A3&B3

and copy this down your whole column of items. You then need another function somewhere as follows:

=COUNTIF(C3:C50,"xd&quot

which will count all the instances of 'xd' that appear, as generated by the A3&B3 formula.

 

[beniez]

Hi Paul

There is another way. To count the Jo Bloggs, use:

{=SUM((A1:A6="Jo&quot*(B1:B6="Bloggs&quot)}

Enter this as an array function (ie don't put the curly brackets in, but SHIFT+CTRL+ENTER to enter it) and it will work.

HTH

Ben

 

[Thanks]

thank you for the replies, both worked perfectly!

 

[DaleWatson123321]

Hi Paul,

Yet another option to become aware of, especially because of its dynamic and powerful capabilitiy, is Excel's "database functionality". This includes database "formulas" (e.g. =DCOUNTA, =DSUM, etc.), and an "extraction" capability. Both of these involve the use of "criteria" where you specify whatever simple or complex conditions needed in order for you to: count, add, or extract the specific records you require.

For example you could create a simple matrix of formulas that would provide Total Sales by Month, by Salesperson.

Or, using more complex criteria, you could produce a report such as: Total Sales by Month by Salespersons who have made in excess of $10,000 in sales of "widget" products, sold to clients between the age of 50-70, living in a rural setting, west of the Mississippi.

There are two primary reason for Excel users NOT having utilized this powerful component of Excel.

1) Microsoft seems to have "downplayed" the use of this component, possibly because they wanted customers to opt for purchasing their optional database (Access).

2) There are two BUGS in this component of Excel. These are encountered when one "manually" attempts to use this component - from the menu: Data - Filter - Advanced Filter. If attempting to extract data to a separate sheet, an ERROR message will tell you this is NOT possible. This is FALSE - it IS possible. Another FALSE ERROR message will tell you it's NOT possible to place your criteria on a separate sheet. Again, this IS possible.

"The proof is in the pudding"...

1) In spite of these FALSE ERROR messages having existed for a CONSIDERABLE length of time, Microsoft continues to ignore them.

2) I'm told that coverage on this powerful component of Excel is covered in a VERY SPARSE manner in books on Excel, and in at least one case of a sizeable book, this topic was NOT covered AT ALL.

3) In spite of an "ancient" version of Lotus 123 (pre-Windows version) having set an EXCELLENT example for Microsoft to follow, Microsoft ignored, and CONTINUES to ignore this "database" component of Excel. They are STILL missing the following 4 VERY useful functions that existed in the "other" spreadsheet BEFORE WINDOWS: a) Data Find, b) Data Modify (Update), c) Data Delete, and d) Data Append.

What does one have to do to get Microsoft's attention ??? Does anyone have any good ideas ???

Notwithstanding the above-mentioned limitations and FALSE ERROR messages, Excel's "database functionality" is STILL VERY useful, and should definitely be considered as an option. Guaranteed, the time you invest in learning to "harness this power" will pay BIG dividends.

...Just my 3¢ worth I hope readers will find this information "motivating" - to the point where you will begin to investigate, experiment, practice, and then utilize this VERY powerful utility.

And, if any of you happen to have connections at Microsoft.......

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Paul,

Because "visitors" are unable to award those "shiny red STARS" you see, I'm only too pleased award a STAR to both iGrant and Ben on your behalf.

It's only AFTER one becomes a Tek-Tips MEMBER that you will see "Mark this post as a helpful/expert post!" - at the lower-left-corner of the contributor's posting. Clicking this results in the "awarding of a STAR" - as a way of giving thanks and recognition for the contribution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[beniez]

Dale

Thanks for the star - wholly undeserved, since I was only recycling something I only learned in this forum a week ago! But you get a star from me for reminding me (again) to look into the Dxxxx etc functions - and their pitfalls.

Regards

Ben