0

I have a sheet with rows as follows (Date formatted as Excel Date, Actual, Difference), with one set of 3 columns for each week of the year.

Values in the first column represented contracted hours for the week for a given client.

The second column will be how many hours the work actually took.

The third is the difference.

In one of the final columns, I need a sum of all the contracted hours - those that are in columns with dates as headers.

Here's what I've tried: =SUMIF(J1:FO1, ISNUMBER(J1), J2:FO2)

Where J1:FO1 are the headers (ex. 1/5/18, Actual, Difference), the ISNUMBER statement resolves to True when J1 is a date (which resolves as a number), and J2:FO2 is the first row of client data.

Although this doesn't throw an error, it also doesn't actually add the numbers in J2:FO2.

Any ideas?

1 Answer 1

2

That is because ISNUMBER(J1) resolves to TRUE or FALSE based on what is in J1. Then the SUMIF() is trying to find where J1:FO1 is TRUE or FALSE not where they are numbers.

SUMIF cannot be used in this manner. It is optimized and wants a string not a formula.

You will need to use something like:

=SUMPRODUCT(--ISNUMBER(J1:FO1),J2:FO2)

or the following Array Formula:

=SUM(IF(ISNUMBER(J1:FO1),J2:FO2))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter.

Both will iterate the range and add the values where the ISNUMBER returns TRUE.

1
  • That first one did the trick. I just had to add a few $ to keep it evaluating the top row. thank you so much. I will accept this answer when SE allows me to in 34 seconds.
    – That Idiot
    Feb 20, 2018 at 21:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .