Sumproduct and Sumifs

[chaboyski]

Hi Guys,

would this 2 be possible to be combined?

heres a scenario:

this is the simplest formula. but the problem is, i needed to use Sumifs to capture selected datas only.

i Tried this:

SUMIFS(Dump!D:D,Dump!B:B,Summary!$A$1,Dump!C:C,Summary!E26) would be what should be like the Column C in the first image
SUMIFS(Dump!G:G,Dump!B:B,Summary!$A$1,Dump!C:C,Summary!E26) would be what should be like the Column B in the first image

would it be possible?

Thank you in Advance!

 

[jasonb75]

You can add criteria to SUMPRODUCT using arrays, for example --(B:B=$A$1)

Either use a realistic number of rows, or dynamic arrays with this. Full columns will be slow to calculate.

You can then do SUMPRODUCT/SUMIFS for the final result.

I'll try and do a full formula later if you get stuck, posting from phone at the moment and they're not easy to write on here.

 

[chaboyski]

thank you! I can send you my sample file but i dont know how to attach it here. - maybe instead of selecting the whole column i can just select some - the downside might be the data would be more than the selected sell.

the below image would show you where the sumifs is getting the data. sumifs was added since i needed to get only data for my company and the extracted raw include everyone.

 

[jasonb75]

maybe instead of selecting the whole column i can just select some - the downside might be the data would be more than the selected sell.

If you know that there would never be more than 2000 rows then you could use that as a the range size. It doesn't have to be exact. Even 100k rows is better than the entire column.

=SUMPRODUCT(Dump!$G$9:$G$2000,Dump!$D$9:$D$2000,--(Dump!$B$9:$B$2000=Summary!$A$1),--(Dump!$C$9:$C$2000=Summary!E26))/SUMIFS(Dump!$G$9:$G$2000,Dump!$B$9:$B$2000,Summary!$A$1,Dump!$C$9:$C$2000,Summary!E26)

Do you know how to create named ranges? You can use them to find the last row containing data.

If you already know how then it will make things a lot easier as I will have much less to explain to you.

 

[chaboyski]

i dont know how to create named ranges but it is all good. data most probably wont be eating more than 2k columns.

with the formula that you have given - just to confirm, you first got the datas from columns D and G and then used the -- to check the other conditions, right?

 

[jasonb75]

That is correct, I used your sumifs formulas from post 1 and based the new formula on that

 

[chaboyski]

yea and im really thankful! every formula fits. but when i try to run use it, it shows #value!. would there be other options?

 

[jasonb75]

Do you have any #VALUE! errors in the columns of the Dump sheet that the formula refers to?

Looking at the formula and your screen capture in post 5, I can think of no other reason for that error to occur.

 

[chaboyski]

YESSSSSS! thank you so much! its the freakin Time. appreciate the help.