Figuring out how much money is out on a certain day

4

4gooddeal

Hello.
I've tried to search for an answer, but no luck. Maybe someone can help me.

I work for a company that distributes samples of their products to professionals to review for feedback. This issue can be thought of as a rental company as well.
I want to know on any given date how much merchandise was out on loan.

Basically I have these columns I need to work with

Value of item
Date Shipped
Date Returned

If "Date Returned" is empty it means that it is still out on loan.

Help would be greatly appreciated!!
 
R

Ray Bicker

Hi,

Assuming Col A is Value, B is Shipped, C is Returned and there are 7 records
try:

=SUMIF(C2:C8, "", A2:A8)
 
4

4gooddeal

Hi,



Assuming Col A is Value, B is Shipped, C is Returned and there are 7 records

try:



=SUMIF(C2:C8, "", A2:A8)





--

Ray Bicker

(e-mail address removed)

Let me clarify.
I am not necessarily seeking the amount that is out currently. I would wantto have the figures historically. In other words if i were to have a column of every single day, say in page2 column A, column B would have a figure,based on a formula that would reflect the amount that was out that day (based on info from Sheet1.) (The ultimate goal is to create a chart)
 
C

Claus Busch

Hi,

Am Tue, 14 May 2013 06:08:55 -0700 (PDT) schrieb (e-mail address removed):
I am not necessarily seeking the amount that is out currently. I would want to have the figures historically. In other words if i were to have a column of every single day, say in page2 column A, column B would have a figure, based on a formula that would reflect the amount that was out that day (based on info from Sheet1.) (The ultimate goal is to create a chart)


try:
=SUMPRODUCT(--(A2:A20<>""),--(C2:C20=""))
or:
=COUNTIFS(A2:A20,"<>",C2:C20,"")


Regards
Claus Busch
 
C

Claus Busch

Hi,

Am Tue, 14 May 2013 15:26:02 +0200 schrieb Claus Busch:
=SUMPRODUCT(--(A2:A20<>""),--(C2:C20=""))
or:
=COUNTIFS(A2:A20,"<>",C2:C20,"")

sorry, I didn't read correctly.
Try:
=SUMPRODUCT(--(Sheet1!B2:B20=A1),--(Sheet1!C2:C20=""),Sheet1!A2:A20)
or
=SUMIFS(Sheet1!A2:A20,Sheet1!B2:B20,A1,Sheet1!C2:C20,"")



Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top