What Formula To Use

[singingsister]

I have a long sheet which monitors when certain documents are sent, returned etc which is listed by company. I have a stats sheet at the front and I want to find a formula that will look for a certain company together with a certain word (i.e sent/returned) to give me a total (ie MacDonalds 12 sent, 4 returned).

How do I do this?

 

[singingsister]

um call me thick but:-
=SUMPRODUCT(--(SupplierRange="Macdonalds"),--StatusRange="Open"))
doesn't work?!

can someone explain it better. I think its:-
=sumproduct(log!b3:b300,"macdonalds"),(log!h3:h300,"open)

is that right?

 

[Richard Schollar]

Brian was just using an example. The formula you want sounds as if it will be:

=sumproduct((log!b3:b300="macdonalds")+0,(log!h3:h300="open")+0)

Give it a try and post back if you hit difficulties

 

[singingsister]

why the +0 out of curiosity?

 

[Richard Schollar]

The arguments (such as Log!B3:B300="MacDonalds") within the Sumproduct return arrays (lists) of True and False, depending on whether each cell in the range matches the criteria. Unfortunately, Sumproduct doesn't handle these True/False lists properly and therefore needs help to turn them into numeric values (in this case 1 for True, 0 for False). One way of achieving this is by adding 0:

True+0 = 1
False+0 = 0

Other ways (which are equally valied) would be to multiply by 1 or to use a double minus. So the following are exactly the same:

=sumproduct((log!b3:b300="macdonalds")+0,(log!h3:h300="open")+0) 
=sumproduct((log!b3:b300="macdonalds")*1,(log!h3:h300="open")*1) 
=sumproduct(--(log!b3:b300="macdonalds"),--(log!h3:h300="open"))

I hope this helps to explain it!

 

[singingsister]

IT WORKS!
Thanks!