COUNT based on 2 criteria
 

I have the formula below that sums the values in Range name
'Purchases' based on 2 criteria. How could I modify this to COUNT the
number of instances of receipts, based on the existing 2 criteria?

=(SUMPRODUCT((Store=123)*(Inventory_No=$I$6),(Purc hases))/10)

I've tried =(COUNT((StoreNo=123)*(Inventory_No=$I$6),(Purchas es))/10)
and =COUNT((SUMPRODUCT((StoreNo=123)*(Inventory_No=$I$ 6),(Purchases))/
10))

But they don't give me the correct answer

COUNT based on 2 criteria
 

=SUMPRODUCT(--(Store=123),--(Inventory_No=$I$6))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
I have the formula below that sums the values in Range name
'Purchases' based on 2 criteria. How could I modify this to COUNT the
number of instances of receipts, based on the existing 2 criteria?

=(SUMPRODUCT((Store=123)*(Inventory_No=$I$6),(Purc hases))/10)

I've tried =(COUNT((StoreNo=123)*(Inventory_No=$I$6),(Purchas es))/10)
and =COUNT((SUMPRODUCT((StoreNo=123)*(Inventory_No=$I$ 6),(Purchases))/
10))

But they don't give me the correct answer

COUNT based on 2 criteria
 

Thank you, but how would that count the number of purchases? (I've
other columns of data in my sheet also)

COUNT based on 2 criteria
 

Try it and see.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
Thank you, but how would that count the number of purchases? (I've
other columns of data in my sheet also)

COUNT based on 2 criteria
 

Just tried that, answer = 7, but should be 2

If I have 10 columns of data and the range name 'Purchases' is in
Column F, how would the formula count the number of receipts, for
Store 123 and Inventory_No in I6, or am I missing something?

COUNT based on 2 criteria
 

That is how that SP formula works. without seeing your data I cannot say why
you get 7 or why you think you should get 2, but assuming Store is column F,
and assuming the formula that you posted woks, this is the same, it just
doesn't multiply by the number of purchases or divide by 10.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
Just tried that, answer = 7, but should be 2

If I have 10 columns of data and the range name 'Purchases' is in
Column F, how would the formula count the number of receipts, for
Store 123 and Inventory_No in I6, or am I missing something?

COUNT based on 2 criteria
 

Bob, Store is in Column A; and Purchases in Column F

COUNT based on 2 criteria
 

So that formula checks where column A = 126 and Inventory_N o = I6, pure and
simple.

Can you post an example dataset that calculates wrongly?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Bob, Store is in Column A; and Purchases in Column F

COUNT based on 2 criteria
 

See below, answer I should get is 2 (receipts of the inventory line)

Store InvID Opening Purchases
123 16 4730 0
123 16 2900 0
123 16 2150 2500
123 16 3870 0
123 16 2950 2750
123 16 5120 0
123 16 4150 0
525 16 6500 0
525 16 4650 0
525 16 4020 0
525 16 3070 0
525 16 2220 1800
525 16 3450 1200
525 16 3880 0

COUNT based on 2 criteria
 

I see why you get 7, there are 7 items where the Store is 123 and the
inventory is 16, but why do you think it should be 2?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
See below, answer I should get is 2 (receipts of the inventory line)

Store InvID Opening Purchases
123 16 4730 0
123 16 2900 0
123 16 2150 2500
123 16 3870 0
123 16 2950 2750
123 16 5120 0
123 16 4150 0
525 16 6500 0
525 16 4650 0
525 16 4020 0
525 16 3070 0
525 16 2220 1800
525 16 3450 1200
525 16 3880 0

COUNT based on 2 criteria
 

Possibly

=SUMPRODUCT(--(Store=123)*--(InvID=16)*--(Purchases0))



Mike

" wrote:

See below, answer I should get is 2 (receipts of the inventory line)

Store InvID Opening Purchases
123 16 4730 0
123 16 2900 0
123 16 2150 2500
123 16 3870 0
123 16 2950 2750
123 16 5120 0
123 16 4150 0
525 16 6500 0
525 16 4650 0
525 16 4020 0
525 16 3070 0
525 16 2220 1800
525 16 3450 1200
525 16 3880 0

COUNT based on 2 criteria
 

Using *AND -- is totally redundant.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mike H" wrote in message
...
Possibly

=SUMPRODUCT(--(Store=123)*--(InvID=16)*--(Purchases0))



Mike

" wrote:

See below, answer I should get is 2 (receipts of the inventory line)

Store InvID Opening Purchases
123 16 4730 0
123 16 2900 0
123 16 2150 2500
123 16 3870 0
123 16 2950 2750
123 16 5120 0
123 16 4150 0
525 16 6500 0
525 16 4650 0
525 16 4020 0
525 16 3070 0
525 16 2220 1800
525 16 3450 1200
525 16 3880 0

COUNT based on 2 criteria
 

Thank you Mike, excatly what I need

Bob, was looking to count the number of receipts for a particular
store and inventory item. My example 2 receipts were receeived for the
data in question

COUNT based on 2 criteria
 

What made it hard was that you didn't say ... where the number of purchases
0.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Thank you Mike, excatly what I need

Bob, was looking to count the number of receipts for a particular
store and inventory item. My example 2 receipts were receeived for the
data in question

COUNT based on 2 criteria
 

BTW, note my comment about redundancy

=SUMPRODUCT(--(Store=123),--(InvID=16),--(Purchases0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Thank you Mike, excatly what I need

Bob, was looking to count the number of receipts for a particular
store and inventory item. My example 2 receipts were receeived for the
data in question