Array Function

[N Harkawat]

take a look at COUNTIF function in help

 

[Egon]

I'm having a mess of a time getting this to work.

I have an array from a report. I need to count the people in the left
column who have a certain value in the right column. I'm doing
something wrong, or its monday morning and I can't seem to get it to
work.

Can anyone help me out here?

J.

 

[Bob Phillips]

=COUNTIF(B1:B100,"xxx")

assuming B1:B100 is the right column.

 

[Egon]

Maybe I didn't explain myself properly.

I have a list of positions in the "A" Column and a list of companies in
the "B" column. I need to get the number of positions in the "A" column
who's company is equal to a certain company in the B column.

CountIF seems to only count within a certain column.

Does that explain it better?

Thanks
J.

 

[Mangus Pyke]

Maybe I didn't explain myself properly.

I have a list of positions in the "A" Column and a list of companies in
the "B" column. I need to get the number of positions in the "A" column
who's company is equal to a certain company in the B column.

CountIF seems to only count within a certain column.

Does that explain it better?

Thanks
J.

=COUNT(IF(A2:A100=[insert criteria],IF(B2:B100=[insert
criteria],1,0),0)

And confirm with Ctrl+Shift+Enter

MP-

 

[Mangus Pyke]

Maybe I didn't explain myself properly.

I have a list of positions in the "A" Column and a list of companies in
the "B" column. I need to get the number of positions in the "A" column
who's company is equal to a certain company in the B column.

CountIF seems to only count within a certain column.

Oops.. make that "SUM" instead of "COUNT" in my answer.

Sorry, waking.

MP-

 

[Egon]

Thanks a ton, that works flawlessly. That's going to save me massive
amounts of time everyday.

Thanks again so much.

J.

 

[Egon]

if I may ask, what does the CTRL+SHIFT+Enter do?

I've never seen that before.

J.

 

[Mangus Pyke]

if I may ask, what does the CTRL+SHIFT+Enter do?

I've never seen that before.

Also called "CSE formulas", this forces Excel to use the data as an
array.

Excel will enter the formula as an array and place curly brackets
around it to identify it as such.

Entering an array by manually including the curly brackets doesn't
work, so Ctrl-Shift-Enter must be used.

MP-

 

[Bob Phillips]

You can also do it without an array formula

=SUMPRODUCT(--(A2:A100="condition1"),--(B2:B100="condition2"))