CODEWITHSUNDEEP
google-sheetsformula
Errious
Errious

Reputation: 53

How can i replace a number within a formula with a value from a specific cell

maybe it is just easy, but i can not find the answer and tried everything i know, i use a formula to count some values in a row using SUMIFS: 

=SUMIFs($B$5:$B,$H$5:$H,$H5,$A$5:$A,"<=665000000")

Problem is, i don´t know how i can use the value i have in A2 to replace the numbers between "<=665000000", maybe someone can explain this for me?

enter image description here

Upvotes: 1

Views: 47

Answers (2)

Karl_S
Karl_S

Reputation: 3554

If I understand you correctly, you want

=SUMIFs($B$5:$B,$H$5:$H,$H5,$A$5:$A,"<=" & A2)

The & will concatenate the two strings. You could also do the same with

=SUMIFs($B$5:$B,$H$5:$H,$H5,$A$5:$A,CONCAT("<=", A2))

And if you need to combine more than one string, use the full worded CONCATENATE() function. Unecessary here, but to give the idea:

=SUMIFs($B$5:$B,$H$5:$H,$H5,$A$5:$A,CONCATENATE ("<", "=", A2))

Note that you may have an issue with the first criteria as well $H$5:$H,$H5. Make sure it returns what you want. This is the same as having it be "=" & $H5

The best thing to do is to check this with 1 criteria at a time when troubleshooting.

Upvotes: 1

Ed Nelson
Ed Nelson

Reputation: 10259

Try:

=SUMIFs($B$5:$B,$H$5:$H,$H5,$A$5:$A,"<="&A2)

The & is a concatenation operator. It concatenates "<=" and the value in A2.

Upvotes: 1

Related Questions