Help with counting

markpdx

New Member
Joined
Sep 19, 2006
Messages
15
I work with spreadsheets to manage various things and currently I am working on a sheet that manages items from multiple vendors. One of the most important parts of this sheet is counting these items from the vendors.

What I want to do is this; if I have vendor1 who has X number of items but some are inactive I want to count vendor1 but leave out the inactive items from the count.

I know that I can use the formula =countif(range,"vendor1") to count the total of all items from vendor1; however I want to write a formula that will count if vendor1 is the text but there are blanks in another range.

So for instance I have range A2:A61 and the text is vendor1 and then in G2:G61 I have blanks mixed with dates that show when items are inactive.

How do I count vendor1 but not include the inactive items in the count?

Any help would be great. Ive tried various things like =countif(range,IF(ISBLANK(range)),"vendor1") and other experiementation with formulas that probably shouldnt go together and I really am needing to get something that works.

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Try this:

Code:
=SUMPRODUCT(  --($A$2:$A$21="Vendor1"),  --($B$2:$B$21="")  )
to count the blank dates for Vendor1
Dufus
 

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
Try this, where colum A contains the vendor names and column G has blanks or text.

=SUMPRODUCT(((a5:a11)="vendor1")*(LEN(g5:g11)<>0))
 

markpdx

New Member
Joined
Sep 19, 2006
Messages
15
Nope neither of those worked. Thank you for the quick responses though.

When I put in the formula that Doug.T wrote it almost gave a backwards result, except it was somehow counting only 3 of the 4 that are nonblank.

Let me try to explain this by typing. I want to do this:

if A2:A61 = "vendor1" and G2:G61 = nonblank then count how many items are in "vendor1"

is that confusing?
 

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80

ADVERTISEMENT

if A2:A61 = "vendor1" and G2:G61 = nonblank then count how many items are in "vendor1"

Can you clarify "How many items"? There are numbers in the "vendor1" column or vendor names?
The macro may be counting only 3 of 4 if some cells look blank but actually contain a space.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Yeah, Doug is right. The formulas both work. Mine was set up to count blanks. His is counting nonblanks. If you got the wrong results, the cells must not be blank. Select the range of cells that are supposed to contain blanks and do Edit>Go To...>Special..., select "Blanks" and click OK. Do the highlighted cells match your understanding of which cells should be regarded as blank?
 

markpdx

New Member
Joined
Sep 19, 2006
Messages
15

ADVERTISEMENT

I was in err last night, too tired I guess. Dufus' formula worked perfectly.

could you please explain this to me so I know how to do similar things in the future and what each item in the formula is?
 

markpdx

New Member
Joined
Sep 19, 2006
Messages
15
So here is the reverse question now.

How do I use Dufus' formula to count the nonblanks, the ones with dates in the cells?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
So here is the reverse question now.

How do I use Dufus' formula to count the nonblanks, the ones with dates in the cells?

1]

=SUMPRODUCT(--($A$2:$A$61=H2),--($G$2:$G$61=""))

2]

=SUMPRODUCT(--($A$2:$A$61=H2),--ISNUMBER($G$2:$G$61))

where H2 houses a vendor of interest like vendor1.
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

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