Help with counting

[markpdx]

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

 

[Dufus]

Try this:

=SUMPRODUCT(  --($A$2:$A$21="Vendor1"),  --($B$2:$B$21="")  )

to count the blank dates for Vendor1
Dufus

 

[Doug.T]

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]

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]

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]

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]

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]

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]

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.

 

[markpdx]

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