countif formula

gina_k14

New Member
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Caliche

Active Member
When there is more than one condition, you must use SUMPRODUCT.

HTH.

gina_k14

New Member
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

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

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

gina_k14

New Member
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!!!

MrExcel MVP
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
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

Replies
1
Views
211
Replies
1
Views
261
Replies
0
Views
75
Replies
5
Views
176
Replies
2
Views
154

1,181,249
Messages
5,928,911
Members
436,634
Latest member
JimHHH

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.

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

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