Formula Question

[roadrunner]

What is the formula for wanting to get the TOTAL number of cells with the word "SGT" in the range A1:A50, BUT ONLY count them IF the word "GO" appears in the range B1:B50? I can get them separatly but not together. Please help.

[kinkyparamour]

try the following

=sum((a1:a65336="sgt"(*(b1:b65336="go"))

Press Ctrl+shift+enter to make the formula work.........

[Ivan F Moala]

On 2002-03-31 17:52, kinkyparamour wrote:
try the following

=sum((a1:a65336="sgt"(*(b1:b65336="go"))

Press Ctrl+shift+enter to make the formula work.........

I think that should be

=sum((a1:a65336="sgt")*(b1:b65336="go"))

[kinkyparamour]

Your right. Thanks for correcting my typeo...

Denny

[Chris Davison]

Also, you may want to trim down those ranges in the formula to your actual A1:A50 - you'll notice the performance difference when you recalc.

If your real data is, in fact, thousands of rows though, I'd suggest you take a look at Excel's database functions : =DCOUNT in particular.

A couple of extra options

[Aladin Akyurek]

On 2002-04-01 02:59, Chris Davison wrote:
Also, you may want to trim down those ranges in the formula to your actual A1:A50 - you'll notice the performance difference when you recalc.

If your real data is, in fact, thousands of rows though, I'd suggest you take a look at Excel's database functions : =DCOUNT in particular.

A couple of extra options [img]/board/images/smiles/icon_smile.gif[/img]

Chris,

You amaze me. You need to make a distinction between a single cell array or SUMPRODUCT formula and one that gets copied down to a huge number of cells.

Lets take the Roadrunner case.

=SUM((A1:A50="SGT")*(B1:B50="GO"))

array-entered, or

=SUMPRODUCT((A1:A50="SGT")*(B1:B50="GO"))

normally entered will not differ much in performance cost compared with DCOUNTA, not DCOUNT.

In order to apply DCOUNTA, you must have labels in A and B, say, Field1 and Field2.

You'll need to enter the following in an area, say, C1:D2.

{"Field1","Field2";
"SGT","GO"}

Now, you can use:

=DCOUNTA(A1:B50,1,C1:D2)

Aladin