1. N

    Sumifs, Sumproduct or is it IF, and Sum

    I am trying to create a formula that looks at results and levels and then calculates the sum of points. ACADAEAFAGAH34CompletedDoingProposed5144 Credit Points258006100max 60 @ 100 level7200min 36 @ 300 level8400max 12 @ 400...
  2. N

    Multiple sheets, multiple criteria formula excel 365 and Google sheets

    So after many hours and lots of assistance I am nearly there. What I would like now is the following, this is my current formula: =SUMIFS(Group1!$N$3:$N,Group1!$D$3:$D,"<="&$B$14) I would like to add to this formula is 3 more criteria. That formula is in H14 and I would like to add H13...
  3. aells

    Sumproduct Question

    I have a formula in a column S that looks like this =-IF(N83="Feed",O83*$E$33*$I$62*(P83/$I$60+$I$61),0)+R83 Column n = who transports Column o = Pickups Column P = round trip distance Column R = Cost Column D = Start Date Cell E33 = Weeks in month Cell i62 = rate - want to make this...
  4. S

    Count Each Row In Dynamic Range Once If Any Cell > 0

    I have a data set with unique IDs in column B and dates in row 2. I am trying to make a formula that looks at a date I've selected, finds that column in the data sheet, looks at at a range that includes the found column and the 11 before it (i.e., a whole year), then counts the number of rows...
  5. C

    Sumproduct/Indirect formula needed

    Hello Can someone please help re-write the formula. Not sure what I'm doing wrong, I get #REF error. =SUMPRODUCT(INDIRECT("'"&C$1&"'!$c$5:$au$54")*(INDIRECT("'"&C$1&"'!$a$5:$a$47"=E2)*(INDIRECT("'"&C$1&"'!$c$1:$au$1"=E2)*(INDIRECT("'"&C$1&"'!$c$3:$au$3"="total")))) C$1 = Worksheet reference...
  6. W

    Sumproduct formula question (no VBA, please)

    Hello excel experts, I have the following formula: =SUMPRODUCT(SUBTOTAL(103,OFFSET($F$26,ROW($F$26:$F$999999)-ROW($F$26),0)),($A$26:$A$999999=$A21)*($B$26:$B$999999=$B$7)) The issue that I have run into is that each time I update my pivot table that uses this formula I run out of resources. I...
  7. C

    SumIF generates a #VALUE!

    Hi all. I got a sum if function, that looks like this: =SUMIF('(PATH)'!$F:$F;">"&(EOMONTH(TODAY(); -1)+1);'(PATH)'!$H:$H)/1000 But it generates an error, because of the closed workbook. Can somebody help me create a SUMPRODUCT or SUM(IF - function?
  8. M

    Sum unique values with multiple criteria

    Hey all, I am trying to create a formula in the that only sums the first unique value in column B but I need this sum to include a secondary grouping criteria which is column A (so only sum unique values for the Apple ground and then only sum unique values for orange and so on). I am not quite...
  9. G

    SUMPRODUCT with multiple AND and OR criteria including partial texts

    I have got an issue that I can't workout. I need to perform a SUMPRODUCT with multiple AND and OR criterias. something like the following...
  10. A

    Subtotal Sumproduct?

    Is it possible to use SUBTOTAL in conjunction with SUMPRODUCT? I'm using the following formula to calculate a weighted average price increase. =SUMPRODUCT(N5:N3400,AQ5:AQ3400)/SUM(AQ5:AQ3400) column N= price increase percentage column AQ= sales I would like to add SUBTOTAL to the formula in...
  11. B

    Weighted Averages ignoring blanks and unrelated columns. For Google Sheets

    Hi! I am having a lot of trouble calculating a weighted average and ignoring blank cells. I understand what to do if the data is in an array, but I am working on a scorecard that will be printed out. The odd formatting is making it very difficult for me to use the sumproduct function and...
  12. C

    SumProduct on a variable length using a mask

    I have been using this formula {which is working fine} SUMPRODUCT( N( MOD( COLUMN( $I37:AJ37)) -6, 7)= 0), $I37:AJ37) +F37 where the first part creates a mask for every 7 column, Middle part is numbers, text and blanks which only every 7th is needed, and the last part is a offset value. I...
  13. Jyggalag

    Make VBA code that posts a cells formula as a value

    Hi all, I currently have the following setup: I have some numbers in column C and D, and I use a simple sum() formula in column B. However, I also want to be able to click on the cells in column B and see the value in the formula bar. But I want to keep the formulas in the meantime. What I...
  14. M

    Multiplying two cells together then summing them together if three criteria are met.

    I have a database with auto generated data. On a separate sheet in the same workbook I can pull the information I need with SUMIFS but in a certain situation I need to multiply two cells first then sum the answers together without adding another column and divide by 12 to get Feet.
  15. A

    Need Help on Sumif using rank based on criteria (which changes)

    Hi Team, This is my first post and apologies if I am overstepping any rules here. I have gone through similar posts, but didnt find a way forward. There are similar threads, but the issue here is a bit different. The problem Statement is as follows - I need to calculate the time taken before a...
  16. I

    Sumifs vs Sumproduct on partially blank cell

    Hey All, I have the below table, and instead of having "B", I have formula that goes : If(xxx,"Text", ""), and as it stands B should then reflect: "". When I use the formula: sumifs(range Y,Range Y, "<>"), the formula reverts 3; but if I use sumproduct ((range X <> "")*(Range Y)) the formula...
  17. P


    Hi there I have a specific excel question and was hoping someone out there might please be able to help. I have this formula which is functioning: =SUMPRODUCT((Income!$A$4:$A23>=B10)*(Income!$A$4:$A23<=C10)*(Income!$F$4:$F23)) However it doesn't work if there are any cells that aren't numbers...
  18. L

    how to calculate sumproduct if row below is different than current row

    I've attached a link to a data table. Here's what I'm trying to do. For each cell in column G, I need to calculate the revenue-weighted average price of the product lines EXCLUDING the product line associated with the row of the cell that I am calculating in column G. Cell G2 is a manual...
  19. W

    VBA code to add repeat formula to each blank row

    Hi there, I'm trying to add a formula into each blank row of my data set in column C. (see cell C5 on image, as an example of where the formula has populated, and cell C11 where it hasn't) The formula in column C is a weighted sum product formula i.e. =($B3*C3)+($B4*C4)+($B5*C5), so this...
  20. C

    Quickest Way to Apply SumProduct to Hundreds of Cells With Different Criteria?

    Hi. I am trying to use the sumproduct formula, but I need to apply it to about 300 cells and each cell has different criteria. I don't want to have to go cell by cell to put each criterion in and am wondering if there is a faster/better way to do this? To explain this further in column A I...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back