# Help with counting

#### markpdx

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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this:

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

Try this, where colum A contains the vendor names and column G has blanks or text.

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

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?

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.

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?

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?

So here is the reverse question now.

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

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.

Thanks for all the responses. All of the formula's work great!

Replies
11
Views
362
Replies
1
Views
195
Replies
2
Views
353
Replies
15
Views
336
Replies
17
Views
935

1,217,764
Messages
6,138,472
Members
450,141
Latest member
Hal5000

### 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.

### Which adblocker are you using?

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