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

[Hero-0952]

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

 

[planetpj]

Use SomeProduct

Book1
	A	B	C	D
1	1	2	3
2	2
3	1
4	6	2
5	2	2
6	Text
7	1	2
8
9
10			2
Sheet1

 

[attc]

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]

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]

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]

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]

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.

Thanks Again!!

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