perform function on criteria in SUMIF function-CodePudding

I am writing a SUMIF function on a range with a format such as:

   A       B
1  1.1     5
2  1.2     5
3  1.3     5
4  2.1     5
5  2.2     5

I want the SUMIF to group values together by their integer, i.e.:

   A    B
1  1    15
2  2    10

I can do this by creating a third column C and applying INT(A) so that:

   A       B    C
1  1.1     5    1
2  1.2     5    1
3  1.3     5    1
4  2.1     5    2
5  2.2     5    2

and (for 1)

=SUMIF(C:C, 1, B:B)

Is there a way to do this in one cell? i.e.

=SUMIF(A1:A5, "int(range) = 1", B1:B5)

I have also tried:

=SUMIF(A1:A5, "1*", B1:B5)

and

=SUMIF(A1:A5, 1&"*", B1:B5)

But these also do not work

CodePudding user response：

You can use SUMPRODUCT e.g.

=SUMPRODUCT(--(INT($A$1:$A$5)=C1),$B$1:$B$5)

INT($A$1:$A$5)=C1 produces an array of TRUEs and FALSEs. The -- operator converts that to an array of 0s and 1s. Then you multiply by the values in B.

CodePudding user response：

Formula in cell F12 is:

=SUMIFS($B$1:$B$5;$A$1:$A$5;">="&E12;$A$1:$A$5;"<"&E12 1)

Drag down

CodePudding user response：

With Excel 365 you can do it like-

 =LET(x,UNIQUE(INT(A1:A5)),y,SUMIFS(B1:B5,A1:A5,">="&x,A1:A5,"<"&x 1),CHOOSE({1,2},x,y))