# sum based on criteria

1. ### VAT Payments Conditional Sum with MOD function--HELP can't figure this out

Hi all Month 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 P&L VAT charge 3 -21 -50 -25 -60 -2 -78 -10 -36 -44 -99 -6 Quarterly Pmt -68 -87...
2. ### Help on Excel Formula using Sum and If

My spreadsheet has a list of customers and invoice amounts and a yes or no that indicates whether or not they have paid. I want to sum only the amounts that have been marked as a yes (for paid.) I tried using the sumif function but it didn't work correctly. My next idea was to do an imbedded...
3. ### Getting the sum of column based on equal dates

Hello everybody. Monthly, I am making a report wherein I need to get the sum of a column based on specific dates. Attached is extracted sample of the data: A B C 1 Value Date Amount Sub Total 2 01/04/2012 89,812.10 3...
4. ### Sum for a specific date range

Hi, I'd like to create a formula that will give me the total number of a specific action based on a specific date range from another column. Layout is... Column A - Transaction Type - Contains: FIW, LIW, etc Column B - Date of Transaction - Contains: dd/mm/yy Column C - Qty Transfered -...
5. ### SUM from first positive number and on

I have a range of numbers in a row that consist of both positive and negative numbers. I would like excel to locate the first positive number and start adding all the number then after including that first positive number. Does anybody have a solution for me. -\$7,514,205,-\$91,005, -\$91,005...
6. ### Sumif?

Hello All! I'm in need of a bit of help. I have a data set as follows: A B <TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=204><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913"...
7. ### Array or other Formulas to Determine Price and Volume Count

Hi, I have a set of data. Date........Price........Volume 7am.........99............10 701am......98.............2 702am......98.5..........12 etc. Record set of 46k rows so looking for the most efficient formula. I want to graph the volume shifts but struggling with an efficient formula to...
8. ### SUM n smallest

Array formula {=SUM(LARGE((\$B\$2:\$B\$50)*(\$A\$2:\$A\$50=D2),{1,2,3}))} works fine, but if I try to find SUM of 3 smallest, formula doesn't work. Why? {=SUM(SMALL((\$B\$2:\$B\$50)*(\$A\$2:\$A\$50=D2),{1,2,3}))}
9. ### Add 2 rows via VBA Help Needed

I need to take the data from one spreadsheet that looks like this: karen 1 3 2 1 craig 2 1 1 1 karen 1 2 1 1 and create a new set of rows that look like this: karen 2 5 3 2 craig 2 1 1 1 There is no real order to the names - just that they may...
10. ### SUMIF with 3 criteria

I need to one formula that gives me the following returns based on 3 criteria. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p> Criteria #1 If the sum is greater than zero (0), then return the average. <o:p> </o:p> Criteria #2 If the sum equals zero (0)...
11. ### Quick help with sum of HLOOKUP

Hi, I have a master table with weights. And there is a row for which I need to do a sum of the associated weights. There would be some blank cells in between as well. Any help would be great. E.g. The sum of the below row should come out as 3. <TABLE style="WIDTH: 536pt; BORDER-COLLAPSE...
12. ### Count based on Color

Is there a way to a do countif like formula based on a cells color? Example we use conditional formatting for cells over or under a value, Red / Yellow / Green. I'd like to have a formula that looks across a row to count the Reds, then in another cell a formula to count the Greens etc. I...
13. ### Sum Data Multiple Criteria

I have a spreadsheet that I require to sum certain cells based on a report code and cost centre and a certain month. I have written the formula to pick out a certain month but am having severe issues trying to do this for the sum of a number of months (see excerpt below) For August i need to...
14. ### Sum values based on a word in a text string

Hi All, really hope you can help!! I need to sum values based on finding a word in a text string, but the word may be in a different place in each string. eg.... South York Distribution Research East York Unit 4 North York Goods inwards The text will always be in the same column and the...
15. ### URGENT! Need HELP ASAP

Hi Everyone, Sorry to be hasty but I need help on this issue. I cannot wrap my head around this. I need to consolidate my sums with two sepearate criteria on this excel sheet. I have my regions Column A/B/C Column A: Region Column B: Class Column C: The Data Region 1/121/DATA Region...
16. ### Tune a value depending on two cells, challenge your knwoledge!!

Hi people, Hi have a challenge for all of you ;) I'm making some country planning due that my company need to make measurements there. In the specification is setted the total ammount of days per country and the minimun days per city. So, once I know more or less the days per city in the...
17. ### Looping Macro Help!

<TABLE width="100%"><TBODY><TR><TD> I have a spreadsheet that has multiple rows for the same date (Col A), each with different product data in cols B-M. I need to be able to read through each row until the date changes (the number of rows changes each day). Whilst the date matches I need to add...
18. ### IF formula

I use multiple workbooks and each work book contains many worksheets with names of customers which contains their order. I would like to know if it is possible to create formula to calculate a product if any customer should order it. For instance, in the workbook "April", if customer A order...
19. ### average if

I need help with averaging with conditions Example if {'FN Project Tracking'!\$M\$17:\$M\$8546=\$B22} and if{ 'FN Project Tracking'!\$G\$16:\$G\$8546="BAU" } and is more then 14 days old average # of days dates are in {'FN Project Tracking'!\$L\$16:\$L\$8546 }{ 'FN Project Tracking'!\$O\$16:\$O\$8546 } any...
20. ### Sum a range based on a criteria

Hello, I have a list of properties and payments recording them in order received eg Prop Payment 1 10 4 5 3 7 3 4 4 9 5 9 1 8 2 9 I then need a sum of all payments for each prop Prop Total 1 (sum of all payments...

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...