SUMIF with INT

[jferrer288]

Hi, I have this table (its spanish so decimals are commas)
I want it to add items as a new total in G66 if the integer in column a is the same as the reference number in A66, but it seems it wont work on a range.
=SUMIF(A14:A58;INT(A14:A58)=A66;G14:G58)
Thanks for the help

 

[jferrer288]

sorry, so if A66=16 the sum in G66 should be equal to g29+g30+g31+g32+g33 = 9961.50

 

[Joe4]

Welcome to the Board!

You cannot use INT with SUMIF like that, but you can use it with SUMPRODUCT, i.e.

=SUMPRODUCT(--(INT(A14:A58)=A66),--(G14:G58))

or in your case:

=SUMPRODUCT(--(INT(A14:A58)=A66);--(G14:G58))

 

[jferrer288]

Welcome to the Board!

You cannot use INT with SUMIF like that, but you can use it with SUMPRODUCT, i.e.

=SUMPRODUCT(--(INT(A14:A58)=A66),--(G14:G58))

or in your case:

=SUMPRODUCT(--(INT(A14:A58)=A66);--(G14:G58))

Doesnt seem to work, thanks for the feedback though

 

[Joe4]

On closer inspection, it looks like the values in column A, in rows 14:58 are entered as Text and not as numbers (you can tell by the little triangle in the corner of the cell).
The INT function only works on numbers. Try converting those values to numbers, and it should work. You should be able to do that with "Text to Columns" from the Data menu.
Just select column A, go to "Text to Columns" on the Data menu, and click "Finish".

 

[Fluff]

Another option if they are text would be

=SUMPRODUCT((LEFT(A14:A58,LEN(A66))=A66&"")*(G14:G58))

 

[jferrer288]

didn´t realize I had to include the "-"
TThanks

 

[Dave Patton]

Try the example in my post and then adapt to your data.
N.B. You can post a concise example with the forums tool XL2BB.

T202105a.xlsm
	A	B	C	D	E	F	G
1
2	16.1						10
3	16.5						20
4	16						30
5	22.5						100
6	25						200
7	15						200
8
9	16		60
2a

Cell Formulas
Range	Formula
C9	C9	=SUMPRODUCT(--(INT(A2:A7)=A9),G2:G7)

 

[Joe4]

You are welcome. Glad we could help.