SUM Monetary Transactions Based On Two Criteria

Feb 20, 2014

I want to SUM monetary transactions based on two criteria.

1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")

I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.

=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)

=SUM((IF(MONTH(REGISTER!$B$3:$B$1000)=1,REGISTER!$G$3:$G$1000,0)))

View 9 Replies


ADVERTISEMENT

How To Pull Transactions Into Template Based On Customer Code

Dec 11, 2012

We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.

I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).

Manual Invoicing Query.xlsx‎

View 1 Replies View Related

Search Page To Bring Back All Transactions Based On A Primary Key

Jun 28, 2009

I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:

Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66

The idea is to enter an account number like 234356 click a button and bring back:

Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66

I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.

View 9 Replies View Related

How To Assign Monetary Value To Percentages?

Jan 6, 2010

I'm brand new to this forum, so please forgive me in advance. I am hoping someone might be able to point me in the right direction. I got a request from my boss and it's something I've never done in Excel and far more advanced than anything I've tried to do.

In my spreadsheet, Columns B-BD are server names, and Rows 2-13 are program names. Inside the corresponding cells all have to display as percentages, and we are trying to display what percentage of each server is being used by each program. In Row 14, each column must total to be 100%. That part is easy, I already have that all setup.

However, the next step requires that each server is assigned a monetary value - one of two monetary values for Virtual or Physical server. Then, somehow I need Excel to calculate the monetary value for each percentage.

For example: if Column B is Virtual, and Row 14 totals up to equal 100%, it also equals $1,000. Say Cell B4 is equal to 50% and B5 is equal to 50%, each cell is also equal to $500. Easy enough in theory, but how should I execute this so that these cells stay in % format, but Column BE titled "Total Cost" displays the monetary value for each Program (row)?

I'm pretty sure there will be some kind of formula so I guess that's what I'm asking... how to calculate it?

I'll attach a screen shot to show you the gist of how it looks so far ...

View 14 Replies View Related

Monetary Values From Web To Excel?

May 18, 2010

Is it possible somehow to "download" specific values from web into specific cells?

I'm creating some currency converter and problem is that values are changeable every day multiple times, so I wana avoid that with entering specific value of specific currency from web into adequate cells.

This site is mine "database": link

View 8 Replies View Related

Excel 2013 :: How To Assign Monetary Value To Number / Quantity

Jul 29, 2013

I am building a spreadsheet for contractors to use to submit home rehab bids. I am using a Windows 8 and Excel 2013. How to assign a monetary value to a number/quantity.

For example, when the contractor indicates 2 toilets need to be replaced and 1 toilet needs to be repaired, I would like to assign the value of $300 to replace a toilet and $150 to repair. Ultimately, I would like the 2 and the 1 to be automatically totaled to read as $750 in the total line at the bottom of the table.

View 1 Replies View Related

Tracking Paid Transactions

Mar 25, 2014

I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.

Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.

View 1 Replies View Related

Sorting Paypal Transactions

Feb 16, 2008

I need to sort all my pay pal transactions I need all my debits in one row and credit in another.

View 14 Replies View Related

Counter For Transactions Within Account

May 11, 2009

I need to have a running counter of transactions within an account.

Solved with: C2=COUNTIF($B$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3

I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.

View 6 Replies View Related

Counting Transactions Per Agent

Sep 3, 2009

I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.

I tried sumif, but its totalling the amount. but i wanted is the number of transaction.

View 9 Replies View Related

Compare Combinations Of Values / Transactions

Jan 25, 2014

I need to compare in and out of money in multiple bank accounts.

Imagine in row one i have all the "INs"
and in row two i have all the "OUTs"

Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship
It can be:

1. Transaction IN 1 = Transaction OUT 3
2. Transaction IN 1 = Transaction OUT 2+4
3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4

So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.

If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.

View 5 Replies View Related

Conditional Cell Color Changes And Transactions

Sep 7, 2009

1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).

1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).

View 4 Replies View Related

Finding Difference In Timing Between Transactions

Oct 13, 2009

I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.

below is my datedif formula, but it obviously only works in a sequential order from top to bottom.

=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"

View 12 Replies View Related

Info For Transactions - Calculate Commissions

Aug 23, 2013

I have a spreadsheet with detail info for transactions. There are multiple columns...but these are the ones I'm concerned with. ie

Cell A4 has the date range ( 1 month) ie "04/01/2013 - 04/30/2013"

below starts on B5
Cell B - Cell D - Cell I
vendor - Qty - Profit
H20 Month $50 2 7.00
H20 $30 Mo Unl T&T 2 4.20
H20 Month $60 21 88.20
Page Plus Unl $55 3 22.29
PagePlus Unl $39.95 6 32.34
etc...
Cell A32 has the date range ( 1 month) ie "05/01/2013 - 05/312013"

and the vendor, qty and profit like above again....

The above

To the right of this I have :

Cell L2 Cell M2 Cell N2 Cell O2 Cell P2 Cell Q2 Cell R2
Company - April - Topup profit - May Topup Profit - June - Topup profit
H20 29 110.60 71 261.10 93 342.95
PagePlus 19 55.05 25 106.72 14 44.70
etc...

at the bottom, I have the totals for each cell from M2 thru R2.

How can i get L2 thru R2 to sum up the detail amounts on Column I by Column B ?

View 1 Replies View Related

Counting Paid Transactions Per Seller Database?

May 6, 2014

Find attached , formula on d2 and e2 , raw data sheet1

Attached Files : counting seller paid.xls

View 3 Replies View Related

Transactions Over A Month Broken Down To Each Product Type

Apr 27, 2009

I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:

=COUNTIF(Licences!E2:E9999, "<39600")-COUNTIF(Licences!E2:E9999,"<39569")

36900 = Jun-08
39569 = May-08

But I need it to also break it down for product a, b, c so I need something else to add to the formula?

View 6 Replies View Related

Temporarily Hide Transactions In Bank Statement?

Feb 3, 2014

I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.

I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.

- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.

- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?

View 1 Replies View Related

Bank Transactions - Grouping / Totaling Like Titles

Jun 1, 2014

I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount

1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set.
Total each set.

2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"

3) formula to find all postage totals and combine for a grand total per category.

usps15.23postage
usps14.32postage
usps5.23postage
fedex5.25postage
fedex10.22postage
shell45.28fuel
shell99.38fuel
qt27.38fuel
qt44.88fuel

View 7 Replies View Related

Count The Closed Transactions Month Wise

Dec 4, 2008

I would like to be able to count all the closed transactions for the month of May and then add Column B if they match with May ....

View 9 Replies View Related

Sumproduct Multiple Daily Transactions By Date And Month

Jun 7, 2009

Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.

PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.

View 7 Replies View Related

Form Sub-totals Quarterly In Typical List Of Transactions?

Feb 21, 2014

How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:

Date Amount Transcation
5/4/14 200.50 bolts
7/5/14 50.10 screws
6/6/14 10.00 bolts and nuts
--------------------------------------------
SubTotal 260.60

12/7/14 10.00 bolts and nuts
10/8/14 40.00 rivets
4/10/15 10.00 screws
--------------------------------------------
Sub Total 60.00

View 1 Replies View Related

Excel 2010 :: Create Balance Ledger To Track Transactions At Different Locations

Dec 16, 2011

So I'm trying to create a balance ledger to track my transactions at different locations.

This is basically what I have:

C4 = numerical value for site A
D4 = balance for site A
E4 = numerical value for site B
F4 = balance for site B
G4 = total balance of both sites

Values for C4 and E4 are manually entered.

D4: =IF(OR(ISBLANK(D3), ISBLANK(C4)), "", D3+C4)
F4: =IF(OR(ISBLANK(F3), ISBLANK(E4)), "", F3+E4)
G4: =IF(AND(ISBLANK(D4), ISBLANK(F4)), "", D4+F4)

I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me

#VALUE!

for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?

I am using Office 2010 on Windows 7.

View 2 Replies View Related

Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

View 5 Replies View Related

Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

View 2 Replies View Related

SUM Based On Certain Criteria

Nov 18, 2013

The Table below outlines a scenario i have..

I am looking for a sum that looks at Colum A: to determine if it is an old version and new. So G2 should If it is marked with the word "New" give me the sum to column F2: otherwise give me the the sum of B2:E2.

Was looking at Sumif but not can't seem to get the formatting right.

A
B
C
D
E
F
G

[Code] .....

View 1 Replies View Related

Sum Based On Criteria

Jun 24, 2006

I've got a sheet called DATA with a series of columns. Column Q has a series of numbers throughout the rows. I need to input a formula in a cell that says, everytime column Q is = 2, calculate the sum of those rows in column N.

The second one is a bit more challenging. A few cells in column F contain the number 1 in them. I need a formula that calculates the average of the cells in column C wherever there is a 1 in column F.

View 7 Replies View Related

Look Up Value Based On 2 Criteria

Sep 6, 2006

I am trying to create a sliding fee scale for a medical practice. Essentially it will categorize patients by family size and income level. The table which the scale is based off of is as follows: The far left column is family size (1-10) followed by 4 monthly income levels (ie. 1000, 1200, 1400, 1600). The table is based in the federal poverty line (FPL). I need to create a lookup formula which will reference this value and generate the appropriate category based on the income and family size of the patient. For example, according to the table a 3 person family which earns less than 1000 is in category A, but between 1000 and 1200 is in category B.

View 9 Replies View Related

Sum Based On 1 Criteria

Jul 18, 2007

I have 1 worksheet which consist of few products for 6 months which I want to
sum up the individual product cost for certain period ie. YTD for 3 month, YTD april, and Q2 (april to June). The result is to appear in another worksheet. Try to use sumproduct, but unable to get what i want..

View 2 Replies View Related

Using Different Assumptions Based On Criteria

Apr 29, 2014

I have a complex workbook that now requires me to use different assumptions based on the label of the assumption (a "year" label) and the relevant year of the month in question (probably using the "Year(A1)" function. However, I do not know how to write a formula that will change assumptions based on whether the year of the relevant cell matches the year of the relevant assumption set. I attached a super-simple spreadsheet as a sample.

View 3 Replies View Related

Lookup Value Based On Three Different Criteria

Jun 6, 2014

I have two workbooks. I'll call them wkbk1 and wkbk2.

I am looking at three cells in the same row in wkbk1.

I need to identify which row in wkbk2 contains those values and then return a value from a cell in the same row in wkbk2.

How do I structure this look up?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved