Figuring out how much money is out on a certain day

  • Thread starter Thread starter 4gooddeal
  • Start date Start date
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!!
 
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)
Click to expand...

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)
 
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)
Click to expand...


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


Regards
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,"")
Click to expand...

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
 
Back
Top