Excel - how do you total columns that are not adjacent
Asked By Lyn Kolf
21-Nov-09 08:44 AM
how do you total columns that are not adjacent but in the same row?
I saw this previous post but cannot figure it out:
until the last cell is BCZ4. So, every interval space of 6 cells. The values
are all in the same row.
reply: Try this
=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))"
I would like to: begin with the amount in D7
subtract amount in E7
add amount in H7
provide answer in I7
is this possible? if so, how?
really appreciate your help...will be a very long
spreadsheet and these calculations will be beneficial.
thank you!
Excel
(1)
SUMPRODUCT
(1)
COLUMN
(1)
MOD
(1)
SUM
(1)
SalesAid
(1)
Dguillett1
(1)
Don Guillett replied to Lyn Kolf
Maybe?
=SUMPRODUCT((MOD((COLUMN(G4:BCZ4),7)=1)*(G4:BCZ4))-e7+h7
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Mike H replied to Lyn Kolf
Hi,
I do not understand which is the question.
Is it this
=SUM(IF(MOD(COLUMN(G4:BCZ4),7)=0,G4:BCZ4))
or something else
Mike
Excel Sumproduct Excel Hi, I have a worksheet called holiday planner, i use sumproduct for plotting the dates. however i need to have another column for whereabouts. also i use a custom if function to determine the whereabout since sumproduct returns number. my formula goes like this SN = array for name SW = array for whereabouts SF = array for FROM ST = array for TO = ((SUMPRODUCT(($G11 = sn)*(sw = "CRA")*(sf< = H$7)*(st> = H$7)))+0.1)*((SUMPRODUCT(($G11 = sn)*(sw = "MNL")*(sf< = H$7)*(st> = H$7)))+0.2)*((SUMPRODUCT(($G11 = sn
Count data in one column indicated by data in another column Excel Column A holds "X" or "" (blank). Column B holds "1", "2", or "3". I need to count the number of 1s, 2s, and 3s, in column B when the cell in column A is blank. I've tried several variations of IF, AND, & COUNTIF - can't get anything to work. Have also tried many variations of SUMPRODUCT found in other posts. None are working for me. Can you please help? Is there
SUMIF-with 2 conditions, need to add range in 2 columns Excel Column a Column b Column c Column d If column d = "specific" and column a = "mary", would like to add columns b and c Please help! Excel Worksheet Discussions Microsoft Excel (1) SUMPRODUCT (1) SUMIF (1) Devenshire (1) Shane (1) Nursemlb (1
multiple sumif or sumproduct Excel Morning all, Column A = Bob Column B = Cat Column C = House Column D = 10 Column E = 5 Column F = 1.6 Column G = 100 I am trying to write a formula that will
Count nonblank cells based on another column Excel Column C has months listed (as text: Jan 2009) and Columns D-N have various reference the month. For example, for all of the Rows that have "Jan 2009" listed in Column C, I want to count all the non blank cells in Column D (labeled "Letter"). I've tried COUNTIF, DCOUNTA, and SUMPRODUCT and feel like I keep missing something. Thanks Excel Worksheet Discussions SUMPRODUCT (1) DCOUNTA (1) COUNTIF (1) SandyB (1) Elkar (1) Nonblank (1) Months (1) SUMPRODUCT should