Need Formular for Multiple Critera condition

[BobT]

I've struggled with this for 3-days - H E L P !

At this point I don't even know what formular type to try anymore.

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 or items i
an inventory list.

o Column B of 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, & if blank it's not sold a
yet).


e.g (where Col B = Book etc, and Col G = $ Value or blank)

1,Book,xx,xx,xx,$3.00
2,Photo,xx,xx,xx,$1.00
3,Book,xx,xx,xx,empty
4,Book,xx,xx,xx,$4.00


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


I need to get the total# of SOLD items for a particular catagory,
don't need the total $ amount earned, just the count or number
of them sold. Anyone know of a way to do this and could show me a
example?

------------------------------------------------------------------------------------
I have tried a number of formulars for exampl
(SUMIF,COUNTIF,DCOUNTA):

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

Yes I am refering to another worksheet (e.g Inventory !) in my formula
(I'm making a Totals sheet) but I get the same results even tryin
formulars on the main sheet without the sheet reference in th
formular.

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
<- It won't let me do a COUNTA with SUMIF.

I'm dedicating a cell and description for each catagory somewhere late
in the spreadsheet if that helps.
-----------------------------------------------------------------------------------

Any Ideas??? - Thanks !



:rolleyes

 

[Don Guillett]

Does this help
=sumproduct((rngA="something")*(rngB=1)*rngc)
ranges must match ie: a1:a100, b1:b100, etc.

 

[BobT]

Thanks Don,

I re-wrote your suggested formular to my earlier question as follows:

=SUMPRODUCT((Inventory!B2:B101="Books")*(Inventory!G2:G101))

It's very close, the formular works partly. It gives me a total for the
selected criteria "e.g books", but gives me the total amount count in a
$(dollar) amount for Col-G.

Quick reflect: Col-B represents the catagory "e.g Books", and Col-G
indicates if the item has been sold or not (e.g $1.00, & if blank it's
not sold yet).

I need to determine how many items for a catagory (Col-B) have been
sold (Col-G). To count the total non-blank Col-G Fields associated
with Col-B = 'Books' fields for example.

It's so close, if I could only alter your suggested formular somehow to
not calculate the dollar amount but instead a count.

Hope that makes sense.

My Thanks in advance to Don an All that would like to HELP solve this !

 

[Don Guillett]

try

=SUMPRODUCT((Inventory!B2:B101="Books")*(Inventory!G2:G101))

=SUMPRODUCT((Inventory!B2:B101="Books")*(Inventory!G2:G101>0))

 

[BobT]

Eureka - T H A N K S D O N !!!

The >0 you suggested for SUMPRODUCT did the trick...

What a relief - My many thanks for your HELP in resolving
the problem I was having with that formula. You were of
great - great assistance

Thanks again my friend.

Bo

 

[Don Guillett]

glad to help