COUNTIF, or am I wrong?? (Solved-TY!!)

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348
Hi All.

I must really be thick this am. I have the COUNTA and the COUNTIF thing figured ...but...I cannot seem to get this straight:

Column A - always contains a "1" if there is any entry in that Column.
Column B - always contains a "2" if there is any entry in that Column.

I need to have the number of "1"s added up in Cell C1 only if there is also a "2" in Column B as well. If there is an erroneous value in A:A - maybe a 3 or something gets in there by mistake - I do not want it counted (as this may occur) so I only want to count the "1"s and not add them.

I thought - at first - to make this an array...but then I couldn't figure why my numbers in C1 were so whacky...I was getting 9...I was using sum. "D'oh!"

I tried to use the COUNTIF, but never figured out a way to set a second criteria...and I want to do this without using Conditional Formatting.

Can I do this? I tried a few examples of things that I found in search but, they were only close and not quite what I was looking for.

Thanks for taking a look!!

Regards,

ViperGTS
This message was edited by ViperGTS on 2002-11-06 09:53
This message was edited by ViperGTS on 2002-11-06 10:50
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
actually I don't think you need SUMPRODUCT. just SUM will do, as follows:

=SUM(($A$1:$A$65535=1)*($B$1:$B$65535=2))

[enter as array formula using ALT+ENTER in usual way]

what I don't understand is (I'm new to these array objects) why:

=SUM(($A:$A=1)*($B:$B=2)) doesn't work and returns #NUM!

can anyone explain?
 

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348
YES! Planet PJ - score!

I am not big on patience...so I suppose that I never would have guessed at this solution. I was looking at this thread: http://216.92.17.166/board/viewtopic.php?topic=25551&forum=2 but considered it to be more than I needed...and I was wrong!

Thank you for your help on this formula!

ViperGTS
PS - I am glad that you were able to get the data from the 'hyper-mess' that was here before I edited the data in the question. LOL! :)
This message was edited by ViperGTS on 2002-11-06 10:12
 

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348

ADVERTISEMENT

ATTC - Thank you for checking in as well. I'd posted before I received the notif that there was another response.

I do not have the answer to this as I too (when fooling with the formula) also received the #NUM notice when changing this to look over all rows in the columns.

ViperGTS
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-11-06 10:10, attc wrote:
actually I don't think you need SUMPRODUCT. just SUM will do, as follows:

=SUM(($A$1:$A$65535=1)*($B$1:$B$65535=2))

[enter as array formula using ALT+ENTER in usual way]

what I don't understand is (I'm new to these array objects) why:

=SUM(($A:$A=1)*($B:$B=2)) doesn't work and returns #NUM!

can anyone explain?

If you are new to arrays as you say are and that self-relection is just about right...

The determined "actually I don't think you need SUMPRODUCT" is incorrect... which proves the point of your self-relection...

{=SUM((Range1=1)*(Range=2))}

is exactly equivalent to

=SUMPRODUCT((Range1=1)*(Range=2))

And...

{=SUM(($A:$A=1)*($B:$B=2))}

or

=SUMPRODUCT(($A:$A=1)*($B:$B=2))

are illegal by design. The formulas that operate on arrays cannot be given whole column/row references (like B:B or 2:2) directly. This is by design.
This message was edited by Aladin Akyurek on 2002-11-06 10:41
 

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348
Aladyn,

Thank you for clarifying this point.
I am never quite sure what I can get away with (when using an array) but this narrows it a bit - and also explains my failures on experimentaion with this variety of formulae. :eek:

Thanks Again!!

ViperGTS
This message was edited by ViperGTS on 2002-11-06 10:49
 

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top