SUMIFS - There's a problem with this formula

[fortnite]

For some reason, this formula:
= SUMIFS( $C$3:$C$53, INT($B$3:$B$53), INT($B3) )
gives me "There's a problem with this formula."

The parenthesis are paired, plain value (not formulas) data in b3:c53, I expect in column DailySumPrice to have repeated sum of prices for each day.

I know I can do it with helper column, but I prefer this method.

Can anybody help?

Here is the Dropbox link to the file:
https://www.dropbox.com/s/q2izuxhv9embrh1/SumIfs Problem.xlsx?dl=1

 

[Joe4]

Welcome to the Board!

I don't think you can do that in a SUMIFS (I don't think it like you trying to apply functions to the ranges you are checking). You may need to use a SUMPRODUCT formula instead.
See if this works:

=SUMPRODUCT(--(INT($B$3:$B$53)=INT($B3)),$C$3:$C$53)

 

[Fluff]

Hi & welcome to MrExcel.
All the xxIFS functions need a range, not an array.
Try

=SUMPRODUCT((INT($B$3:$B$53)=INT($B3))*($C$3:$C$53))

 

[fortnite]

Thank you for the prompt answer (Fluff and Joe4),

Your formula works, but actually, I needed MINIFS (but used SUMIFS as an example).

The initial formula I was trying to implement was:
= MINIFS( C3#, INT(B3#), INT(B3#) )

My idea is to have repeated min amounts per day.

New link:
https://www.dropbox.com/s/7q4gf6tudcynqzf/MinIfs Problem.xlsx?dl=1

B3# and C3# are spilled array formulas.

Now I understand that xxxIFS can not use arrays as parameters.

How I can implement the desired function (MIN), with the expanding range?

 

[fortnite]

****
IN B3# and C3# arrays are spilled array formulas.
***

 

[Joe4]

Your formula works, but actually, I needed MINIFS (but used SUMIFS as an example).

I don't understand this.
Why would you post some totally unrelated formula and not the actual thing you are looking for?
Please do not do that!
Please be honest with us in what you actually want.

 

[Fluff]

If you actually have those div0 errors,try

=MIN(FILTER(IFERROR(C3#,99999),INT(B3#)=INT(B3)))

 

[fortnite]

@Joe4: I was trying to make it as simple case as possible, I did not try to be dishonest. I had the best intentions. I was thinking, if I can have the answer for simple SUMIFS, I could change it to my specific needs. Sorry if I got you upset.
@Fluff: your formula works with eliminating #DIV/0! errors. Two problems exist:
1. in one column I want MIN function, in the next MAX, in the third AVERAGE, if I hardcoded 9999 in MIN formula, -9999 in MAX formula, in AVERAGE I can not put a value
2. it would be preferable if the formula could return the spilled array

 

[Joe4]

@Joe4: I was trying to make it as simple case as possible, I did not try to be dishonest. I had the best intentions. I was thinking, if I can have the answer for simple SUMIFS, I could change it to my specific needs. Sorry if I got you upset.

There is no need to oversimplify things on our account - it is best to always be totally transparent and honest in exactly what you are after.
You should also never assume that completely different Excel functions operate the same way. Some may, but many do not.

I see that you are new here, so I just want to make sure that you understand how you should post. The main issues with posting in the manner you did is that I have seen it start heated arguments in the past because:
1. Responders felt like they wasted their time coming up with a solution to a question that was not what the OP really wanted (in this case, it was luckily just a quick, short answer, but if it was a larger problem that someone spent a lot of time working on, they could be understandly upset).
2. People can feel like they are being manipulated with the old "bait and switch". We have seen many instances where a user seems to post a simple straightforward question, only to add multiple layers of complexity after someone replies. Once again, this can cause hard-feelings and arguments, especially since those people may not have replied original if the knew the true scope of the issue.

So my goal here is to try to help you get the best help available, along with keeping a good reputation on this board.
The best way of doing that is to be totally forthcoming about exactly what you are looking for right from the very beginning.
I hope you adopt this approach going forward.

Happy Excel-ling!

 

[Fluff]

For the Average use "" or 0 instead of the 9999