Need the right function...

[KJL]

I'm working with two worksheets; one has the following columns: (1)
name of sponsor, (2) level of sponsor ($ amount), (3) number of passes
for that sponsor. The data on this spreadsheet is updated each time a
new sponsor is obtained. The second worksheet has the following
columns: (A) listing of sponsor levels ($ amount), (B) the projected
number of sponsors at that level, (C) the associated number of passes
for that sponsorship level, and (D) the total passes for that level (b
x c).

What I am trying to do is to created a Column E that, for each sponsor
level, would search Column ii and return the number of sponsors at that
level. I would like these cells to update each time a sponsor is
added.

For example, Column 2 will have dollar amounts such as $1,000, $2,000,
$3,000, etc. in random order as each sponsor is obtained. Column A
would identyify each sponsor level available (i.e. $1,000, 2,000,
etc.), I want the cell in Column E that corresponds to the $1,000
level row to search Column 2 and return the number of times that $1,000
appears in the designated search area. I would then be able to
compare my Projected number of sponsors (Column B) with the Actual
number of sponsors (Column E).

Any suggestions?

Thanks in advance!

 

[JE McGimpsey]

For example, Column 2 will have dollar amounts such as $1,000, $2,000,
$3,000, etc. in random order as each sponsor is obtained. Column A
would identyify each sponsor level available (i.e. $1,000, 2,000,
etc.), I want the cell in Column E that corresponds to the $1,000
level row to search Column 2 and return the number of times that $1,000
appears in the designated search area. I would then be able to
compare my Projected number of sponsors (Column B) with the Actual
number of sponsors (Column E).

One way:

On second sheet:

E2: =COUNTIF(Sheet1!B:B,A2)

Where Sheet1!B:B corresponds to your column 2.

 

[Jay]

I'm working with two worksheets; one has the following columns: (1)
name of sponsor, (2) level of sponsor ($ amount), (3) number of passes
for that sponsor. The data on this spreadsheet is updated each time a
new sponsor is obtained. The second worksheet has the following
columns: (A) listing of sponsor levels ($ amount), (B) the projected
number of sponsors at that level, (C) the associated number of passes
for that sponsorship level, and (D) the total passes for that level (b
x c).

What I am trying to do is to created a Column E that, for each sponsor
level, would search Column ii and return the number of sponsors at that
level. I would like these cells to update each time a sponsor is
added.

For example, Column 2 will have dollar amounts such as $1,000, $2,000,
$3,000, etc. in random order as each sponsor is obtained. Column A
would identyify each sponsor level available (i.e. $1,000, 2,000,
etc.), I want the cell in Column E that corresponds to the $1,000
level row to search Column 2 and return the number of times that $1,000
appears in the designated search area. I would then be able to
compare my Projected number of sponsors (Column B) with the Actual
number of sponsors (Column E).

Any suggestions?

Thanks in advance!

I'd like to help but find your post a little confusing, and
contradictory in parts.

You state that "Column 2 will have dollar amounts such as $1,000" but
earlier say that column B contains "the projected number of sponsors at
that level".

I'm confused by your column references - are columns ii, 2 and B the
same? If you re-post your request but using the following, I'll try & help:

1) Use only letters for columns (A,B etc.)
2) Make it clear which column is on which sheet
3) If 'level' means 'amount' use 'amount'

Also, what does "the associated number of passes for that sponsorship
level" mean?

I *think* I know what you need, and will be glad to help if you can make
things a little clearer.

Regards

Jason

 

[KJL]

Sorry, Jason. I'll try to clarify.

Worksheet 1, Column C (W1,CC) (between Rows 8 & 65) hold a dollar
amount. It is the amount that a sponsor has agreed to pay for their
sponsorship. There are seven specific levels of sponsorship ranging
from $320 to $4,200. So one of those seven numbers will be in each
row of that column between Rows 8 & 65.

On Worksheet 2, Column B, I have listed the seven sponsorship levels on
Rows 9-15. In Column F of the same worksheet, I would like to get a
count of the number of occurances of each specific number from W1, CC.
For example. In Column F, Row 9, I would like to put a formula that
will look in W1, CC, and count the number of times that $4,100 occurs
between Rows 8 & 65. Then in Column F, Row 10, I want the number of
times that $3,200 occurs in W1, CC.

Did that help at all???

KJL

 

[Jay]

In Worksheet two, cell F9 enter the following:

=COUNTIF(sheet1!$C$8:$C$65,B9)

& copy the formula down to F15 using the auto-fill handle.

HTH,

Jason