Countif and and

[Guest]

Please read below. It is works, but I am looking for something else. How I
add up the number of the bottles that ordered in SB and session 1.

Thanks,
Jeff


:
=SUMPRODUCT((C1:C1000="SB")*(D11000=1)*(E1:E1000<>""))

 

[Ardus Petus]

You previously wrote (quote: "I need to know how many people

ordered water in unit and session.")

Now, if you want the number of bottles, enter:
=SUMPRODUCT((C1:C1000="SB")*(D11000=1);(E1:E1000))

Cheers,

 

[Guest]

I understand what you did, but it wont work for some reason.. I uses those
from another sheet like called TRACKING LIST which i know how and i used the
number from another sheet where the function have =E2-(SUM(G2C2)) in one
cell.

What I did, but it wont work.
=SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING
LIST'!D11000=1);('TRACKING LIST'!E1:E1000))

It gave me an error...

Sorry for bugging you..

 

[Guest]

even the single sheet wont work...

You previously wrote (quote: "I need to know how many people

Now, if you want the number of bottles, enter:
=SUMPRODUCT((C1:C1000="SB")*(D11000=1);(E1:E1000))

Cheers,

 

[Ardus Petus]

Send me your workbook as an attachment to (e-mail address removed) , or
upload it to http://cjoint.com/ and post back the link.

Cheers,

 

[Ardus Petus]

Replace the semicolon ( by a plain comma (,):
=SUMPRODUCT(('TRACKING LIST'!C1:C1000="SB")*('TRACKING
LIST'!D11000=1),('TRACKING LIST'!E1:E1000))

I use a french version of Excel, and often forget to replace my ;'s by ,'s

Cheers

 

[Guest]

It is working. I figured that you have differnet version for French. Im happy
with it, but there is one more that i dont understand. If you look in
TRACKING LIST tab. If i am looking for a last name, "SCHICK". I will use
Ctrl-F to find that name, but it wont work. Why?

Thanks..

 

[Ardus Petus]

You must tick "Values" instead of "Formulas" in "search in" options

HTH

 

[Guest]

One more thing, Sorry.... what is the function that use SB and 1 and B.. I
tried =SUMPRODUCT((C1:C1000="SB")*(D11000=1)*(D11000="B") and it wont
work.

Thanks.

 

[Dave Peterson]

You're counting the number of cells that have a 1 in D1:1000 and at the same
time have a B in D11000.

That number is going to be 0.

Did you mean to use the same column for both 1 and B?

 

[Guest]

Yes,

In the column of D you will find 1 or 2 or B or STAFF. Everything had to be
sort by unit/session like you see the pervious email. I wanted to add up for
the people who are in 1 and B. It still have to sort by unit..

Thanks.

 

[Guest]

Yes,

In the column of D you will find 1 or 2 or B or STAFF. Everything had to be
sort by unit/session like you see the pervious email. I wanted to add up for
the people who are in 1 and B. It still have to sort by unit..

Thanks.