Countif

[boyntoni]

Hi,

First off, just to let you guys know, I am using Excel 2003.

My problem involves, what I believe should be the COUNTIF function. Essentially, I want to use to count based on two "ifs". In other words, I want to count the number of "1"s in column C, as long as it also has a "1" in column A. I want to proceed by doing the number of "1"s in column C, with a "2" in column B, and etc.

I was thinking of using 2 COUNTIF's, but I do not believe that is the way to go. I also tried using an IF and a COUNTIF, but that did not seem to work either.

The second question I have is how to make the window inside the window. I remember briefly seeing one, but what I mean is cutting a table off from the rest of the spread sheet so that it has its own separate scroll bars.

Thank you very much for your help.

[sxhall]

boyntoni,

use this formula....

=SUM(IF(A1:A7=1,IF(C1:C7=1,1,0),0))

You need to press an hold CTRL and Shift when you press Return to get the {} mark's around the formula so it looks like the below.

{=SUM(IF(A1:A7=1,IF(C1:C7=1,1,0),0))}

You can change the ranges in red to suit (both need to cover the same amount of rows) and you can change the criteria in blue as well e.g. look for 2's

The first IF will check to see if column A contains a 1
The second IF will check to see if column C contains a 1
If they both meet the criteria it will count as 1, this will be applied and totaled for the ranges given.

Sxhall

[boyntoni]

Thanks so much for the help.

Just out of curiosity, why are the { } necessary?

Also, any idea on the window within the window?

[NBVC]

This instead:

=SUMPRODUCT(--(A1:A7=1),--(C1:C7=1))

will allow you to ENTER normally...

For second question, do you mean Window|Split?

[boyntoni]

For the SUMPRODUCT - How would I enter it exactly, or, what would be in place of the --?

[NBVC]

You enter it as per my formula...leave the -- 's . These are coercers... they coerce TRUE/FALSE to 1/0.

All you need to change is the ranges to cover and the 1's to whatever you want to test.