Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing.
Take a range of cells as follows
Col A B C
2 1 300 2
3 2 450 4
4 3 700
5 4 900
6 5 1000
7 6 1200
Take these formulas:
Cell A10:
=sumif(A2:A7,"<="&C2,B2:B7)
Cell A11:
=sumif(A2:A7,">="&C3,B2:B7)
Cell B10:
=sum(B2:B7)
Cell A12:
=(A10+A11-B10)*-1
Cell A12 will have the results you want. Hopefully you get the idea.
Basically you sum all the data elements you don't want in your range, subtract that from the total sum of the range and the remainder is the sum of the range you want.
I didn't do much bug checking for corner cases, but hopefully this will give you enough of a start to solve the problem?
To @Jacob's claim of impossibility, I refute it thus... (but thanks for the improved speed)
using:
=sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1))
with the following functions will do what you want.
/**
* Sums cell values in a range if they have the given background color
*
* @param {String} color Hex string of color eg ("#ffffff")
* @param {Array.Array} range Values of the desired range
* @param {int} startcol The column of the range
* @param {int} startrow The first row of the range
*
* @return {int} Sum of all cell values matching the condition
*/
function sumIfBgColor(color, range, startcol, startrow){
// convert from int to ALPHANUMERIC - thanks to
// Daniel at http://stackoverflow.com/a/3145054/2828136
var col_id = String.fromCharCode(64 + startcol);
var endrow = startrow + range.length - 1
// build the range string, then get the background colours
var range_string = col_id + startrow + ":" + col_id + endrow
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getColors = ss.getRange(range_string).getBackgrounds();
var x = 0;
for(var i = 0; i < range.length; i++) {
for(var j = 0; j < range[0].length; j++) {
// Sometimes the cell background is eg 'white' rather than '#ffffff'.
// I don't know why - I think it's a bug.
// so we remove that inconsistency with colourNameToHex
// courtesy of Greg at http://stackoverflow.com/a/1573141/2828136
if(colourNameToHex(getColors[i][j].toString()) == color) {
x += range[i][j];
}
}
}
return x;
}
/**
* Takes a colour string and returns it to a hex string. If a non-matching string is
* passed, it will return the argument as is - for this situation it means that a
* hex string can be passed to it and be returned as is. This is not for production.
*
* @param {string} color Must be either a colour name or hex string of color eg ("#ffffff")
*
* @return {object|string} hex string of color eg ("#ffffff") or the argument given.
*/
function colourNameToHex(colour)
{
var colours = {"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff",
"beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2","brown":"#a52a2a","burlywood":"#deb887",
"cadetblue":"#5f9ea0","chartreuse":"#7fff00","chocolate":"#d2691e","coral":"#ff7f50","cornflowerblue":"#6495ed","cornsilk":"#fff8dc","crimson":"#dc143c","cyan":"#00ffff",
"darkblue":"#00008b","darkcyan":"#008b8b","darkgoldenrod":"#b8860b","darkgray":"#a9a9a9","darkgreen":"#006400","darkkhaki":"#bdb76b","darkmagenta":"#8b008b","darkolivegreen":"#556b2f",
"darkorange":"#ff8c00","darkorchid":"#9932cc","darkred":"#8b0000","darksalmon":"#e9967a","darkseagreen":"#8fbc8f","darkslateblue":"#483d8b","darkslategray":"#2f4f4f","darkturquoise":"#00ced1",
"darkviolet":"#9400d3","deeppink":"#ff1493","deepskyblue":"#00bfff","dimgray":"#696969","dodgerblue":"#1e90ff",
"firebrick":"#b22222","floralwhite":"#fffaf0","forestgreen":"#228b22","fuchsia":"#ff00ff",
"gainsboro":"#dcdcdc","ghostwhite":"#f8f8ff","gold":"#ffd700","goldenrod":"#daa520","gray":"#808080","green":"#008000","greenyellow":"#adff2f",
"honeydew":"#f0fff0","hotpink":"#ff69b4",
"indianred ":"#cd5c5c","indigo ":"#4b0082","ivory":"#fffff0","khaki":"#f0e68c",
"lavender":"#e6e6fa","lavenderblush":"#fff0f5","lawngreen":"#7cfc00","lemonchiffon":"#fffacd","lightblue":"#add8e6","lightcoral":"#f08080","lightcyan":"#e0ffff","lightgoldenrodyellow":"#fafad2",
"lightgrey":"#d3d3d3","lightgreen":"#90ee90","lightpink":"#ffb6c1","lightsalmon":"#ffa07a","lightseagreen":"#20b2aa","lightskyblue":"#87cefa","lightslategray":"#778899","lightsteelblue":"#b0c4de",
"lightyellow":"#ffffe0","lime":"#00ff00","limegreen":"#32cd32","linen":"#faf0e6",
"magenta":"#ff00ff","maroon":"#800000","mediumaquamarine":"#66cdaa","mediumblue":"#0000cd","mediumorchid":"#ba55d3","mediumpurple":"#9370d8","mediumseagreen":"#3cb371","mediumslateblue":"#7b68ee",
"mediumspringgreen":"#00fa9a","mediumturquoise":"#48d1cc","mediumvioletred":"#c71585","midnightblue":"#191970","mintcream":"#f5fffa","mistyrose":"#ffe4e1","moccasin":"#ffe4b5",
"navajowhite":"#ffdead","navy":"#000080",
"oldlace":"#fdf5e6","olive":"#808000","olivedrab":"#6b8e23","orange":"#ffa500","orangered":"#ff4500","orchid":"#da70d6",
"palegoldenrod":"#eee8aa","palegreen":"#98fb98","paleturquoise":"#afeeee","palevioletred":"#d87093","papayawhip":"#ffefd5","peachpuff":"#ffdab9","peru":"#cd853f","pink":"#ffc0cb","plum":"#dda0dd","powderblue":"#b0e0e6","purple":"#800080",
"red":"#ff0000","rosybrown":"#bc8f8f","royalblue":"#4169e1",
"saddlebrown":"#8b4513","salmon":"#fa8072","sandybrown":"#f4a460","seagreen":"#2e8b57","seashell":"#fff5ee","sienna":"#a0522d","silver":"#c0c0c0","skyblue":"#87ceeb","slateblue":"#6a5acd","slategray":"#708090","snow":"#fffafa","springgreen":"#00ff7f","steelblue":"#4682b4",
"tan":"#d2b48c","teal":"#008080","thistle":"#d8bfd8","tomato":"#ff6347","turquoise":"#40e0d0",
"violet":"#ee82ee",
"wheat":"#f5deb3","white":"#ffffff","whitesmoke":"#f5f5f5",
"yellow":"#ffff00","yellowgreen":"#9acd32"};
if (typeof colours[colour.toLowerCase()] != 'undefined')
return colours[colour.toLowerCase()];
return colour;
}
Best Answer
Custom functions are created using Google Apps Script, so no, it's not possible to create a custom function without scripting.
The alternative is to use an add-on or third-party app, maybe one that use Artificial Intelligence, low-coding apps, etc, to do the scripting job for you or to hire someone for that.
References