Used this in Excel and worked perfectly for me! Thanks!

@ExcelSmith

2 жыл бұрын

Thank you. Glad it helped.

this is one of the most vodoo magic formulas I have ever used. You are a genius!!

@ExcelSmith

2 жыл бұрын

Thank you for the compliment. I'm glad you liked it.

Thank you. Needed a solution to place a number at every nth column. SEQUENCE was giving me a challenge. I incorporated your solution into SEQUENCE and it worked wonders. =IF(MOD(SEQUENCE(,240,COLUMN($C$1),1),COUNT(COLUMN($C$1:$E$1)))=1,B8,0) Row 1 is =SEQUENCE(,240,1,1) B1 Contains the number I want to be repeated every COUNT(COLUMN($C$1:$E$1)).

@ExcelSmith

Жыл бұрын

Thanks for the comment. That's an awesome equation. I'm glad the video was helpful.

Amazing video, thanks. I would need another extension of this formula. I need the sum of the QTRs, however for a particular product lead. How can I put filter on both row and column at the same time? Appreciate your help.

Can you help me calculate the total sum from the sum-product formula? S.NO EXPENSES HEAD Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 Bank 1 Bank 2 Bank 3 Bank 4 I need total of BANK 1 by sumproduct formula

Can you type the formula here and a short description what it does

@ExcelSmith

2 жыл бұрын

Thanks for the comment. Here’s the final equation from the video: =SUM(IF(MOD(COLUMN(F$6:R$6),COUNT(COLUMN(J$6:M$6)))=MOD(COLUMN(I$6),COUNT(COLUMN(J$6:M$6))),F8:R8)) For a given row, this equation sums the values in columns spaced n columns apart. That is, every 2nd column, or every 3rd column, etc. For example, if we place the equation in cell A2, and modify the cell references, we could build the equation to sum every other column starting with column B. That is cells B2, D2, F2, H2, etc. Or, we could tell the equation to sum the values in every 3rd column; cells B2, E2, H2, K2, etc. The video walks through each piece of the equation and how to customize it to fit the layout of your data.

i tried this in google sheets its not working there

@ExcelSmith

2 жыл бұрын

Thanks for the comment. Give this a try: =ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(MOD(COLUMN(F$6:R$6),COUNT(COLUMN(J$6:M$6)))=MOD(COLUMN(I$6),COUNT(COLUMN(J$6:M$6))),F8:R8))), 1, 1)

i tried this on excel, went over the formula a few times it does no seem to work, :(

@ExcelSmith

2 жыл бұрын

Sorry to hear it's not working for you. Are you receiving an Excel error or are you getting a value but it's different from what you expect? A common culprit can be not entering the equation as an array formula. To submit the equation as an array formula, press CTRL + SHIFT + ENTER while the cursor is in the formula bar. Please let me know if that doesn't fix it for you.

@martinbyrne5243

2 жыл бұрын

@@ExcelSmith Thank you for the reply, that was exactly the problem. it now works :) wondering can you hep me add another part. if i had a negative number every other month and i only wanted to count the negative number would that be possible. i have tried adding , F8:N8,"

@ExcelSmith

2 жыл бұрын

Thank you for the compliment. If your goal is to count or sum only the negative values, regardless of their column, you could use COUNTIF or SUMIF: =COUNTIF(F8:R8,"

ALTRNATIVE SOLUTION: =SUMIF($F$7:$Q$7,"*TOTAL*",F9:Q9)

@ExcelSmith

2 жыл бұрын

Thanks for the comment. You're spot on with your alternative solution. SUMIF works great when there is a commonality in the column headers, like "QTR" or "TOTAL" in the dataset in this video. The formula in this video would be an alternative to SUMIF when the column headers don't have anything in common to pass into the second parameter of the SUMIF function. Or, if the goal is to sum only a subset of items that share a common value. For example, summing only Months 1, 3, 4, and 6 in this dataset.

@LaloinLondon

2 жыл бұрын

ALTERNATIVE SOLUTION #2: =SUMPRODUCT((MOD(TRANSPOSE(SEQUENCE(12)),4)=0)*(F9:Q9)) ignoring the headers

@ExcelSmith

2 жыл бұрын

That's a great solution using the Excel 365 and Excel 2021 SEQUENCE function. This shows two of the things I like most about Excel: there's usually more than one way to accomplish something and Excel is constantly adding new features and functionality. Thanks for sharing.