Hi Everyone,
This is my formula:
=SUMPRODUCT(INDEX('Report'!$D$6:$O$28*(MOD(ROW('Report'!$D$6:$O$28),2)),0,MATCH(BULK$C$10,months,0)))
So the above formula sums every other row within the range in the column i choose. I choose the column by inputing my column choice in cell C10 in tab BULK.
Now some cells in the range include text that I would like to exclude when i sum so I dont get a #Value!
Can anyone help please?
Thanks,
Hi,
Can you upload your workbook since SUM & SUMPRODUCT generally ignore text anyway so I'm struggling to understand the problem.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
When you use * to multiply that causes a #VALUE! error if there's any text in 'Report'!$D$6:$O$28 because you can't multiply by a text value, try changing the syntax to this:
=SUMPRODUCT(INDEX('Report'!$D$6:$O$28,0,MATCH(BULK$C$10,months,0)),MOD(ROW('Report'!$D$6:$O$28),2))
Audere est facere
Hi,
So for some reason i tried using the formula and it didnt work for me.
Below is the workbook I am working on.
The yellow box is where I type in the month I want and the red boxes represent the information I am trying to find. So in the even rows box, I want it to sum all the even rows for a particular month and in the odd rows box i want it to sum all the odd rows for that particular month.
Please help!
Sample Workbook.xlsx
The syntax works but you need to adjust the references for the setup in your sample - this formula will give the sum of the odd rows for the relevant month
=SUMPRODUCT(INDEX(A2:L27,0,MATCH(O7,A1:L1,0)),MOD(ROW(A2:L27),2))
and for the even rows
=SUMPRODUCT(INDEX(A2:L27,0,MATCH(O7,A1:L1,0)),1-MOD(ROW(A2:L27),2))
Daddylonglegs, would you know how to change the formula in order to sum every third cell within the column range?
Based on the data you provided, try this.
Add a helper column at he end and use this, copied down...
=MOD(ROW(),2)
Then for the even rows, use this...
=SUMIFS(OFFSET($A$1,1,MATCH($O$7,$A$1:$L$1,0)-1,26,1),$M$2:$M$27,0)
change the ,0) at the end to ,1) for odd
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much daddylonglegs! It worked!
Thank you to everyone else as well
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks