Sum Non Adjacent Columns based on Criteria Excel

Sum Non Adjacent Columns based on Criteria Excel

how to sum values based on criteria in another column in excel
excel sumproduct
vlookup and sum all matches in columns in excel
sum based on column and row criteria
sum alternate columns based on criteria
sum(index match multiple criteria rows and columns)
excel sumif
difference between sum and sumproduct in excel

I need to be able to sum the values in non-adjacent columns (i.e. D and E), using a criteria based on another column.

I Found recently in excel forum a response to the question on : "How sum Multiple columns based on a criteria...".

Based on the Formula =SUMPRODUCT((A4:A9="Football")\*(B4:D9)) the result for "Football" is 24.

What I need to Know is, if there are a way of calculating a sum only for the columns [B] and [D] - (B4:B9) + (D4:D9), avoiding to use the fomula below:

=SUMPRODUCT((A4:A9="Football")\*(B4:B9))+SUMPRODUCT((A4:A9="Football")\*(D4:D9))

In this case thw result for "Football" would be: (4+2)+(1+2)+(1+3) = 13.

many thank's in advance,

Leopoldo Fernandes


In the fashion you are trying it with a SUMPRODUCT it would look like:

=SUMPRODUCT((A4:A9="Football")*(B4:B9+D4:D9))

And another fashion, to exclude a certain column from the range B4:D9 could be:

=SUMPRODUCT((A4:A9="Football")*(COLUMN(B4:D9)<>3),B4:D9)

But another (possible better way) would be to use multiple SUMIF:

=SUMIF(A4:A9,"Football",B4:B9)+SUMIF(A4:A9,"Football",D4:D9)

EDIT:

To explain the second formula; it does first check column A against your lookup value returning an array of TRUE and FALSE values. We then check our range of values B4:D9 (might as well make that B4:D4) and only return TRUE in the matrix if it is not column number 3. Now we made our matrix, the second parameter can be the full range so SUMPRODUCT knows whichs values to pick up and add.

As you can see, the formula returns 13 to me, not 15.

Excel formula: Sum columns based on adjacent criteria, To sum or subtotal columns based on criteria in adjacent columns, you can use a formula based on the SUMPRODUCT function. In the example shown, theĀ  To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions. In the example shown, the formula in L5 is: = SUMPRODUCT ( -- ( MOD ( COLUMN ( B5:J5 ) - COLUMN ( B5 ) + 1 , K5 ) = 0 ), B5:J5 ) How this formula Excel SUMPRODUCT Function.


You could also try this:

=SUMPRODUCT((A2:A7="Football")*B2:D7*ISNUMBER(FIND(B1:D1,"Week1|Week3")))

The formula would need to be adjusted if one column header could be a substring of another:

=SUMPRODUCT((A2:A7="Football")*B2:D7*ISNUMBER(FIND("|"&B1:D1&"|","|Week1|Week3|")))

This is potentially a bit more general because you could specify several column headers.

Excel formula: Sum matching columns, To sum values in columns by matching matching column headers, you can use a matching column headers, you can use a formula based on the SUMPRODUCT function. This expression returns TRUE if a column header begins with "a", and FALSE if not. Excel formula: Sum columns based on adjacent criteria. Summing Values Based on the Criteria of a Non-Empty Adjacent Cell Summing Values Based on the Criteria of a Non-Empty Adjacent Cell While working on reports, you want a formula that will give you a sum of values based on the criteria of a corresponding non-empty cell. You can find the output by using SUMIF & SUMPRODUCT functions.


My approach is essentially the same as the 2nd approach provided by JvdV.

=SUMPRODUCT((A4:A9="Football")*(B3:D3<>"Week 2")*B4:D9)

Cheers :)

Sum Non Adjacent/Contiguous Cell By Criteria, Hi,Here are the cell reference and values:A1=2B3=5D9=12A5=3So the above cells are to be summed. Criteria: ">2"So the result should be 20. Click the AutoSum button ?. Then click on each of the cells which you want to add, and ensure that you type in a comma to separate each cell in the formula. So this turns out to be fast as you are clicking the cells with the mouse and pressing the comma with your other hand. So an example is =Sum(A2,C2,G2,I2)


Non-adjacent columns with Sumif, The sumrange is fine as the columns are adjacent, i.e. A10:B20 but the Can Excel fill bagel flavors? It's not clear what your sum criteria is. Sum cells based on column and row criteria with formulas. Here, you can apply the following formulas to sum the cells based on both the column and row criteria, please do as this: Enter any one of the below formulas into a blank cell where you want to output the result: =SUMPRODUCT((A2:A7="Tom")*(B1:J1="Feb")*(B2:J7))


Quickly sum non-contiguous cells, Sometimes you may want to calculate the sum of a group of non-continuous or non-contiguous cells. Do you enter in each cell address like =A2+C2+G2+I2? Re: SumIf Non Adjacent/Contiguous Cells. To count the number of times B4:AS4 contains a value greater than 0 for every 2nd column, starting with Column B, try


Excel SUMIFS: Sum Alternate Columns based on Criteria and , Sum values based on criteria in another column with Pivot table in Excel. Sum values based In Excel, you can use formulas to quickly sum the values based on certain criteria in an adjacent column. 1. Sorry, this video could not be played. I have used the SUMIF function many times on worksheets that have the same type of information in a "range". Now I need to use the SUMIF function in a spreadsheet where the "ranges" are not adjacent to each other. For example in range J1 to J289 I only need every third numbered row for the SUMIF function.