how many items for a catagory have been sold

[BobT]

I have a situation where we need to determine 'how many items for
catagory have been sold'.


The Excel spreadsheet looks something like this:

o Rows in the spreadsheet (Row2 and on) represent records items in th
inventory list.

o Column B in the list represents a catagory selection for a recor
(e.g it's a Book, Photo, etc)

o Column G in the list indicates if the item in the record has bee
sold or not and if so how much (e.g $1.00, blank not sold as yet)


I know how to determine a total number or count for a catagory: e.
=COUNTIF(Inventory!B:B,"Books")


I need to get the total# of SOLD items for a Catagory. Anyone know of
way to do this and could possibly show an example?

------------------------------------------------------------------------------------
I have tried a number of formulars for example (SUMIF,COUNTIF):
e.g =COUNTIF(Inventory!B:B,"Books"),(COUNTA(Inventory!G:G)). It doe
not like the formular combination and ends up suggesting
a subtractioin or multiplier for exampl
=COUNTIF(Inventory!B:B,"Books")-(COUNTA(Inventory!G:G)).

Excel didn't like
=COUNTIF(COUNTIF(Inventory!B:B,"Books"),(COUNTA(Inventory!G:G)))
and
Excel didn't like: =SUMIF(Inventory!B:B,"Books"),COUNTA(Inventory!G:G)

I'm dedicating a cell with description for each catagory somewher
later in the spreadsheet if that helps.


Ideas??? - Thanks !

 

[johnnyray00]

I'm not sure if your command line is correct but,
Make sure you identify your variables.

Dim countif As Long, inventory As Long

and so o

 

[Jim]

How many:
=COUNT(G2:G100) counts the cells with a number
=COUNTA(G2:G100) counts cells with ANY entry
How much:
=SUM(G2:G1000)

 

[Anders S]

Hi,

With the category, Book, Photo, etc, in I2

=SUMPRODUCT(--(B2:B35=I2),--(G2:G35<>""))

gives the number of sold items for the category, and

=SUMPRODUCT(--(B2:B35=I2),G2:G35)

gives the total sales amount.

HTH
Anders Silven