1. D

    Counting Unique Dates within a Date Range With One Critieria (with Photos)

    Hello All! I'm trying to create a formula that counts the number of unique dates within a date range based on the type of item. I've tried several different formulas and researched ways to do it, but each formula was so specific to the poster's specific problem, that it did not work so I...
  2. O

    Text + SUM of cells decimals error

    I'm working on a break list for my team at work. I want a very simple calculation to count the values in cells and return their number, with the text "currently on break: " in front of it. If I just use =SUM(C4:C16) it works perfect and returns me the number, but using ="Currently on Break...
  3. M

    Sum Frequency with Wildcard

    Morning all, I've got a working Sum Frequency formula which counts unique matches. =SUM(--(FREQUENCY(IF((Driver!$A$2:$A$1973='Data Selection'!$A$1)*(Driver!$G$2:$G$1973="No"),MATCH(Driver!$D$2:$D$1973,Driver!$D$2:$D$1973,0)),ROW(Driver!$D$2:$D$1973)-ROW(OFFSET(Driver!$D$1,1,))+1)>0)) It works...
  4. H

    Group By SUM

    Hello, I have very simple data, that I want to group by SUM As the uploaded image shows. My Data has 3 columns, first comes Index, Numbers then Group, which I want to fill according to specific limit cell H2. I started by summing like cumulative column, then divided the result by the limit and...
  5. D

    Sum up concatenation values

    Hello, Is there any way to sum up the numeric values of concatenations? Example below. The data displayed in cells A2:D2 are a result of some concatenations. In cell E2 I would like to be able to sum up those concatenated values. In this case, E2 should come up as 137 days. Stipulations: I...
  6. M

    Sum Index Match with multiple matches

    Looking at the below example, I'd like to be able to get the total hours of the individuals in column H by pulling their hours from Column E. Notice if there is more than one instance of someone's name, it only sums the first result. How might I be able to get the sum of a person's hours...
  7. M

    Rolling Average Ignoring Blanks

    Hello, I'd like to find the 3 day rolling average of two columns while ignoring blanks. Looking at M7 it would be something similar to that formula but ignoring blanks in J and K while automatically finding the previous 3 non blank values. I've tried using offset, large, and various if...
  8. P

    Combine and Sum Lookups across multiple rows using an array formula

    Hi, I need to convert several currencies across multiple rows into GBP by looking up a conversion table and then provide the sum in GBP. I haven't been able to come up with an elegant formula (an array type formula maybe) to keep it short and simple. for the SUM in GBP, I've got the...
  9. G

    PowerPivot CALCULATE SUM

    Hi, would appreciate any help with this, I'm trying to do a calculate sum if in PowerPivot, example of how it workls in Excel below, but am unsure how to replicate in PowerPivot. Thanks in advance for any help. Gav.
  10. J

    HELP - Complex SUMIF - SUM of PRODUCTS of two cells IF another cell is within a specified date range

    Hi all, I am new to this forum and so apologise if I have posted this to the incorrect thread, etc. What I am trying to do is quite simple in theory, but I cannot get a variation of SUMIFS, SUM and IF to do the job. The context is this: I am looking to create a resource for a client couple...
  11. L

    For every cell value that contains string, sum another cell

    I am not sure the best way to describe this. I have a spreadsheet with the following headers: Date; Ticker; Transaction Type; # of Shares; $ / Share; Cost Under transaction type, the cell will contain "Buy" or "Sell". I would like to make two additional headers that are "Sum of Buy"...
  12. F

    Pivot Table, Value criteria set to sum, hide zero's

    I have a data source that deals with items going to customer on deposit. If they take one widget, there is a sale of 1, when they return the widget, there is a sale of -1. resulting in a Zero. I have attached a picture. Over hundreds of customers and thousands of transactions, we can be...
  13. I

    Need help to sum live data in 2 week increments.

    My goal is to understand which two week increments of a marketing campaign were most/least effective. Column A: live data for a marketing campaign (leads generated) Column B: Sum up the live data up until 2 weeks after the start date of the campaign. After the 2 weeks is complete, I would...
  14. F

    SUMPRODUCT IF value equals

    Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column? Please also note that values start in...
  15. S

    hh:mm:ss average problem

    hello, I have data that present at hh:mm:ss eg 00:01:52 but when I try to average I get the DIV error and when I try to sum it comes out as 00:00:00 I have tried the different formatting but nothing changes.
  16. F

    SUM IF Duration Is Between Certain Values

    Hi All! Could you please kindly advise as to how to sum duration values that fall between certain ranges? My custom format is set to [h]:mm and cannot be changed. Let me illustrate what I'm trying to do with an example. Columns A & B usually contain 9:00 & 17:30 respectively, however, at...
  17. T

    Find the combination that gives the minimum sum of squares

    I have a number of vectors A,B,C,...etc as a pair of numbers such as below: x y 1 A 2 4 2 B 5 6 3 C 3 9 I want to list or at least find the combination that produces the minimum x^2 + y^2, by taking combinations by 2 or 3 vectors. I've tried solver unsuccessfully...
  18. H

    Sum of alternate columns with subtotals

    Hi I have a report which pulls values from 2 sources (13 from each) for each month of the year, so 312 columns in total. For each line, I need to subtotal each source per month to compare these totals, and also get the annual totals for comparison. How can I get this? Thanks in advance.
  19. A

    VBA for total and subtotal

    Hello, I have a spreadsheet with column I where there is a lot of data. I have the 1000 which is the sum of the 2x500 (bold) The 2x500 are the sum of the data (first one is 200 =300 and second one is sum of 5x100) I would need a VBA that would populate the formulas taking in consideration the...
  20. D

    Counting distinct values as well as giving a sum

    Hello, I am after some assistance in getting some information out of my database which is causing me some problems. My data shows each individual journey that a truck has taken and I am trying to show the number of journeys each truck did in a given county on a given day and a given shift...

Some videos you may like

This Week's Hot Topics