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

StevenPMoffat
StevenPMoffat used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Please post a sample workbook
Ryan ChongSoftware Team Lead

Commented:
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 Expert
Top Expert 2014

Commented:
@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
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

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

Author

Commented:
Sorry.  Thought I had posted the original sample file to work with.  Here it is.
Top Expert 2014

Commented:
no file
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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

Author

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
Top Expert 2014

Commented:
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

Author

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.
Top Expert 2014

Commented:
You could name the range of date headers.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Steven,

please find attached.  =SUMPRODUCT(--(Planning2[[#Headers],[11/27/2015]:[4/8/2016]]>=$G$17)*(Planning2[@[11/27/2015]:[4/8/2016]]))
Book4.xlsx

Author

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 Expert
Top Expert 2014

Commented:
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?

Author

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.
Top Expert 2014

Commented:
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 Expert
Top Expert 2014

Commented:
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

Author

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???
Microsoft Excel Expert
Top Expert 2014
Commented:
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

Author

Commented:
Thanks for persevering with me.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are most welcome Steven, i am glad i was able to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial