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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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...
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top