Countif only if another column is not blank...

corsabrin

New Member
Joined
Dec 12, 2008
Messages
6
Hello,

I have a list of parts in column A that is about 200 rows or so. There are only a dozen parts or so that repeat multiple times. I then have about 20 columns of data for each part. Column K is "Customer".

What I need to do is count the number of each part in column A. That was done with a simple countif formula. Now I need to count each part in column A only if a cumster name is listed column K (this column is left blank until a customer is added). I can't seem to get this to work. Let me know if you need me to be more specific.

Please help!

Thanks!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
if the data was in a1:a200 and the customer name was in column k, something like (in example, enter in l1) =if(k1<>"",countif($a$1:$a$200,a1),"") would count how many times the value in a happens in the entire range only if there were a customer name...is that what you were looking for?
 
Upvote 0
If you're doing the count by product in some sort of summary table (ie 20 rows -- 1 per product) I would first suggest you try using a Pivot Table.

Failing that you can use a Sumproduct.

If we assume in A210 you have the first product to summise:

B210:=SUMPRODUCT(--($A$1:$A$200=$A210),--($K$1:$K$200<>""))

The above would count the number of times product as specified in A210 appears in range A1:A200 where Customer is not blank.
 
Upvote 0
DonkeyOte...Could you explain the sumproduct formula you used above. I often see that used and it is obviously more efficient however i truly don't understand how it works. Is there a brief explanation of that method? Thanks.
 
Upvote 0
That didn't seem to work. It only returned the numer of times the part appears in colunm A. Here is an example:

A K
Part Customer
Widget A XYZ Corp.
Widget B
Widget A
Widget B Joe Smith
Widget A ABC Corp
Widget C

There are three "Widget A" with two customers listed. I need excel to only count the two parts with the customer listed.

So in this exmaple the result shold be:

Widget A 2
Widget B 1
Widget C 0

Hopes this helps.

Thanks!
 
Upvote 0
EB08,

Bob's page here is a good read on the subject:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

In this specific case here is an example with smaller sample:

Excel Workbook
AK
1ProductCustomer
21A
32B
43
54D
61
72A
83
94A
101
111C
121D
131
142A
153A
164
174F
185A
191F
202
21
22Product
2314
Sheet4


Sumproduct is calculated as follows:

Excel Workbook
ABCD
1Formula Location:Sheet4'!$K$23
2Formula:=SUMPRODUCT(--($A$1:$A$20=$A23),--($K$1:$K$20""))
3Result:4
4Delimiter(s):,*;
5Component Part(s):"--($A$1:$A$20=$A23)""--($K$1:$K$20"")"
6Array Row(s):2020
7Array Column(s):11
8
9Calculation:Result(s)Result(s)Total(s)
101010
112111
123010
134000
145010
156100
167010
178000
189010
1910100
2011111
2112111
2213100
2314010
2415010
2516000
2617010
2718010
2819111
2920000
SPA_121208_201440
 
Upvote 0
I did in l1 =if(k1<>"",a1,"") and copied it down then created a list of all products...for example in m1 i would have the product apple. in n1 i put =countif($l$1:$l$200,n1)...i believe it got the answer you would be looking for but there is probably a more efficient way out there.
 
Upvote 0
Thanks DoneyOte! That works if I use specific cell references (A1:A200), but I would like to use just column A (A:A) since this spreadsheet is constantly growing and shrinking as data is added and deleted.

If I use the formula:

=SUMPRODUCT(--(A:A="Cidermill"),--(K:K<>""))

I get a #NUM! error. What am I doing wrong?

Thanks!
 
Upvote 0
Pre-XL07 you can't use entire column references with Sumproduct, that said you should not set your ranges to be too large anyway as it will impact performance... give yourself some breathing room for sure but the larger the ranges you use the greater the impact the formulae will have on your file as a whole in terms of performance. You could look into using dynamic named ranges but without knowing the particulars of your setup it's hard to comment (in terms of blanks interspersed in ranges etc...) -- if you do a search on dynamic named ranges you should find plenty of examples if you opt to go that route.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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