sumproduct

Hello people! I am quite new to proper work in excel and I need some help! I have this table with sales and revenues from the months december 2014 and january 2015, the sales and revenues are made in 5 currencys, FX rates are given in another sheet . I need to calculate everything in euros...

I've got data organised in the following way: 2 first columns are identifiers and the rest of the columns are values by year (a year per column). So I need to summarise it in a different table based on 2 identifiers an by year. So each value in the summary table should match the combination of...

Hello all, I am having a problem, I thought I got it all figured out with sumproduct function. However, it doesn't work as it should. I am trying to count all the cells containg "2" in columns AM:AO, but only if "1" is found in columns AJ:AL. Ideally a formula that can be further filtered, for...

Hi All, I'm not sure if this needs a Countif formula, a Sumproduct formula or something else. I have the following sheet and I'm tying to do a count based on multiple criteria I need to count based on the following Criteria in the Sheet "Trg Register" COUNTING CRITERIA In sheet "Trg...

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...

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...

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...

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...

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...

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...

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?

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...

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...

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...

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...

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...

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...

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.

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...

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...