countif formula

gina_k14

New Member
Joined
Jul 25, 2004
Messages
27
Not sure if I should be using the COUNTIF formula for the following...

I have a summary worksheet which lists categories A to D, and what I want to find is the number of cells/items on a DIFFERENT worksheet where the criteria are: column D is equal to A and column L is greater than 20%.

Any suggestions???
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Caliche

Active Member
Joined
Mar 26, 2002
Messages
339
When there is more than one condition, you must use SUMPRODUCT.

HTH.
 

gina_k14

New Member
Joined
Jul 25, 2004
Messages
27
That doesn't seem to work.

I don't need to multiply the 2 columns, just count the number of rows/items where the 2 conditions are met.

I don't know if you can use the AND function within a countif formula...
 

Caliche

Active Member
Joined
Mar 26, 2002
Messages
339
Please specify a little more:

Suppose your summary worksheet is "Summary" and the other worksheet is "Other":

you need column "D" of wich worksheet being equal to "A" (text) or to column "A" of which worksheet?

column "L" of wich worksheet?.

HTH
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

=SUMPRODUCT(--(TargetSheet!$D$2:$D$100 = "A"),--(TargetSheet!$L$2:$L$100 > 0.2)
 

gina_k14

New Member
Joined
Jul 25, 2004
Messages
27
Sorry for the confusion.

On the "Summary" worksheet I have:

Category
A
B
C
D

Then on the "Other" worksheet I have 100 rows of items with several columns of information for each one.

What I want to count on the "summary" page is how many items are classified as "A" (the catergory column on the "other" worksheet is column D) WHERE the percentage is greater than 20 (the percent column on the "other" worksheet is column L).

Same formula would apply on the "summary" worksheet for B, C, and D.

Hope that clears it up!!!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
gina_k14 said:
Sorry for the confusion.

On the "Summary" worksheet I have:

Category
A
B
C
D

Then on the "Other" worksheet I have 100 rows of items with several columns of information for each one.

What I want to count on the "summary" page is how many items are classified as "A" (the catergory column on the "other" worksheet is column D) WHERE the percentage is greater than 20 (the percent column on the "other" worksheet is column L).

Same formula would apply on the "summary" worksheet for B, C, and D.

Hope that clears it up!!!

Why don't you try that formula? It looks at the D-range for A's and at the L-range for percentage values greater than 20%. When both hold, the record is counted in. If this is not what you're after, give a small sample from the relevant columns of data from the sheet whose name you still did not reveal.

Here an exhibit showing what that SumProduct formula does...
Book2
DEFGHIJKL
1
2A0.19
3B0.25
4C0.21
5A0.25
6D0.22
7C0.18
8A0.25
9B0.21
10
11
12
13A2
14B2
15C1
16D1
Sheet1


E13:

=SUMPRODUCT(--($D$2:$D$9=D13),--($L$2:$L$9>0.2))
 

Caliche

Active Member
Joined
Mar 26, 2002
Messages
339
gina_k14 said:
I don't need to multiply the 2 columns, just count the number of rows/items where the 2 conditions are met.

quote]

It's important you know that SUMPRODUCT is useful not only to multiply, but to count, as Aladyn's formula shows it.

Caliche
 

Forum statistics

Threads
1,148,047
Messages
5,744,493
Members
423,880
Latest member
CRE_finance_guy

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