Sum up values on Row in Excel based on comparison to values in headers.

I would like to be able to sum up the values in a row, where the values that I include are based on comparing the header values to a set value, a date for instance.   In this case, I want to sum all the values in the various columns wherein the column heading (a date) is later than a fixed value.  As that fixed value changes, the summation will adjust accordingly.
StevenPMoffatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Please post a sample workbook
Ryan ChongSoftware Team LeadCommented:
in general, you probably can use the SUMIF / SUMIFS formula in this case, but as mentioned, try post a sample file so to make things easier.
Professor JMicrosoft Excel ExpertCommented:
@StevenPMoffat

please see attached.  were you looking for solution like this?

2015-12-16-13_21_50-Microsoft-Excel-.png
formula
=SUMPRODUCT(--($D$1:$O$1>=$B$1)*($D$2:$O$17))

Open in new window

Book1.xlsx
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

StevenPMoffatAuthor Commented:
Getting closer.  I updated the file as I want the SumProduct to be row specific.   I also want the matrix in a table.   In this example, it seems to be skipping the data in Columns H,I,K???  When I zero them out, the sum doesn't change.  

Also what is the purpose of the "--" in the formula do?
Also would be nice if the formula could refer to the table header row as opposed to absolute cell references like $D$1:$O$1, but when I do that, the sums come back 0.
Book2.xlsx
StevenPMoffatAuthor Commented:
Sorry.  Thought I had posted the original sample file to work with.  Here it is.
aikimarkCommented:
no file
Professor JMicrosoft Excel ExpertCommented:
Hi Steven,

please find attached. i have fixed the formula based on your latest requirement.


the formula uses now table header and does the calculation based on the row

double negative forces formula to return the TRUE and FALSE to 0s and 1s  it is simple a math operation.  if we change the double negative to +0 or *1 it would still do the job.  i usually use double negative which is the same as +0 or *1

=SUMPRODUCT(--(Table1[#Headers]>=$B$1)*(Table1[@]))

Open in new window

Book2.xlsx
StevenPMoffatAuthor Commented:
Sorry, I wish the original example file was loaded.  Not sure why I can't get it loaded, but when I applied your concept to my real data file, I am getting some errors.  I believe it is because I want the formula to be part of the table (one of the columns) and the references you use for #Headers and [@] get confused and introduce a circular reference.   All I am getting is 0s.   See attached file for latest incarnation.    Is there any other way to reference the table headers and the rest of the columns without including the first or previous columns?
Book4.xlsx
aikimarkCommented:
This version of the formula gets rid of the circular references.
=SUMPRODUCT(--(Test!$D$23:$W$23>=$G$17)*(Test!$D24:$W24))

Open in new window

StevenPMoffatAuthor Commented:
Is there a way to do this without having to refer to specific cell locations and use some sort of table reference?   LIke
=SUMPRODUCT(--(Planning2[[#Headers],[11/27/2015]:[4/8/2016]]>=$G$17)*(Planning2[@[11/27/2015]:[4/8/2016]]))
There is no circular references, but for some reason it doesn't matter what is in cell G17.
aikimarkCommented:
You could name the range of date headers.
Professor JMicrosoft Excel ExpertCommented:
Steven,

please find attached.  =SUMPRODUCT(--(Planning2[[#Headers],[11/27/2015]:[4/8/2016]]>=$G$17)*(Planning2[@[11/27/2015]:[4/8/2016]]))
Book4.xlsx
StevenPMoffatAuthor Commented:
Professor.   It doesn't seem to be working.
With the file you sent me, with a date of 12/11/2015 in G17, the first row should only sum up to 32 and not 64.   64 is the entire row, with 32 in the column with date of 11/27/2015.
Professor JMicrosoft Excel ExpertCommented:
Steven, i got a bit confused here, so excuse me.

isn't the criteria just the dates or the number of cells?   becuase lets say, your date is 12 november 2015 and then the values in the row 24 , one of them is unser 27 november and the other one is under 25 december, meaning that both of these dates are greater then the G17 hence it is counting it.  why should it not count it then?  can you please elaborate?
StevenPMoffatAuthor Commented:
Put in a date of 12/11/2015 in G17 and the value under 11/27/2015 shouldn't be summed up, but it is.   I can tell, because if I zero out the value under 11/27/2015 the total changes.
aikimarkCommented:
I might suspect that the values inside the square brackets are being interpreted incorrectly as numeric values and not as date values that match  column headers.
Professor JMicrosoft Excel ExpertCommented:
Steve,

can you please elaborate why the value under 11/27/2015 should not be counted?
as we can see that this amount under 11/27/2015 should be excluded becuase the date 11/27/2015 is greater than the date of 12/11/2015   Correct?

if value udder as per your statement need to be excluded then what is exactly the criteria? in cell G17
StevenPMoffatAuthor Commented:
The date of 11/27/2015 is actually less than 12/11/2015 and not greater than as you just posted.  Therefore, it shouldn't be counted, but it is???
Professor JMicrosoft Excel ExpertCommented:
Ok Steve, i found where the problem was.

whenever a date is used as header of column, it loses its original input value and gets converted as non-numeric value.

so, i amended the formula using addtional function of DATEVALUE and now it works.  please find attached.
FIXED-IT.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StevenPMoffatAuthor Commented:
Thanks for persevering with me.
Professor JMicrosoft Excel ExpertCommented:
you are most welcome Steven, i am glad i was able to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.