how to sum cells meeting row & column condition

Still not sure quite what you're after.

Are you looking for something that will sum all values in any Lay Hrs column when there are any values in Attn columns?
- This is what eleyaz's formula is doing (as I understand it)

The formula I posted is just to add up any value in a Lay Hrs column where the next Attn column has the value "Late". This seemed to be the formula required for cell Q3.
You would need to adjust the formula for cells R3 and S3 to look for "(P)" and "SR".

Originally Posted by divi123

my data ranges from the date feild 26/06/13 to 25/07/13.

How does the data set grow, are there further rows or columns?
Can you upload a wider sample to see?
What errors are you experiencing?


[edit] Your reply came while I wrote mine

Originally Posted by divi123

=Sumproduct((B2:P2="Lay Hrs")*(B3:P3)*(B3:P3="Late")) but getting a value error.. think it shud be worked even better..

Something like this might work better (in that it is looking in the correct places) but I think the #VALUE error is coming from 'multiplying' TRUE/FALSE results with non-numeric values (in the *(B3:P3) bit).
=SUMPRODUCT((B2:P2="Lay Hrs")*(OFFSET(B3:P3,0,1)="Late")*(B3:P3))
or simpler
=SUMPRODUCT((B2:P2="Lay Hrs")*(C3:Q3="Late")*(B3:P3))
Not sure how you are going to get around that if you're set on using SUMPRODUCT.

Guys thanks, i got the results wen i corrected the workings little

=SUMPRODUCT((E$4:FW$4="Attn")*(E5:FW5="Late")*(D$4:FV$4="Late Hrs")*IF(ISNUMBER(D5:FV5),(D5:FV5),0))
(Ctrl+shift+enter)

it perfectly suits my requirement

bt do explain me the purpose of using IF & ISnumber here..

Also tel me y its taken from 'E' wen the data ranges from "D" (E$4:FW$4="Attn")*(E5:FW5="Late")

Good stuff. yes, that would be the way to get the value for each row rather than the whole data set. GJ.

Each thing that you are multiplying together within the SUMPRODUCT produces an array of elements. The first two arrays are the ones to check for "Late" in the cell one to the right of the cells you want to add up and so are offset one cell to the right compared to the other arrays. This means that when evaluated they will have their true/false values in the same relative positions as the other arrays.

The IF/ISNUMBER part is to deal with the #VALUE response error, as described in post 6 above. All the values in the arrays need to be either true/false or numbers. This part simply checks the values in the relevant array and replaces them with a zero if not a number. The other arrays do not need this validation because you are not multiplying the values, but simply making a logical comparison of each one.

HTH

Ok, I don't have the facility to upload a sheet or image at the moment (or use Excel for that matter) so I'll try explaining without them.

Imaging a simple sheet with the values a, b & c in the first three cells of the first row and the numbers 1, 2 & 3 in the first three cells of the second row.

A B C
1 2 3

Then elsewhere you have the formula:
=SUMPRODUCT((B$1:D$1="C")*(B2:D2=3)*(A$1:C$1="B")*(A2:C2))

The first pair of arrays would return as:
{FALSE,TRUE,FALSE}
{FALSE,TRUE,FALSE}
This is because they are finding the desired values of "C" and 3 in the second position of the identified range in both cases.

The third array would return:
{FALSE,TRUE,FALSE}

The fourth:
{1,2,3}

So the first two are offset to the right by one place so they check the cells one to the right of the desired return (2, in this example) but have their TRUE results in the same relative position in the returned arrays. This is important as when the arrays are ANDed together ("*") they do so like this:
FALSE * FALSE * FALSE * 1 = 0*0*0*1 = 0
TRUE * TRUE * TRUE * 2 = 1*1*1*2 = 2
FALSE * FALSE * FALSE * 3 = 0*0*0*3 = 0

Hope that makes sense. Have to leave the house for a bit so will come back for part 2 later.

Right, the IF/ISNUMBER part...

Because we are multiplying values together then this needs to be something Excel is happy doing.
2 * 2 = 4, Excel is happy with this.
TRUE * 2 = 2, Excel can do this (when the TRUE/FALSE is a boolean value and not a string) because it evaluates TRUE as 1 and FALSE as 0.
"Purple" * 2 = #VALUE, Excel can't work this out as it doesn't make much sense. The #VALUE return lets you know what's going on.

You were returning the #VALUE error initially because part of the mathematical process you were asking for involved multiplying a number by a string. So how do we get rid of string?

The ISNUMBER function tests whether the argument supplied is a number or not. Because this is an array formula we can pass it an array of values for it to work through. In this case we want to have it evaluate the final array of the values that will be returned, (D5:FV5).

If we were to have the first three cells of the first row of a sheet contain:
1,2,F
and then use the array formula
{=ISNUMBER(A1:C1)}
it would return:
{TRUE,TRUE,FALSE}

If we then put that in an IF function and let the "if TRUE" result be our original array and the "if FALSE" result be a zero...
{=if(ISNUMBER(A1:C1),(A1:C1),0)}
...then the return from this array formula would be...
{1,2,0}
...which can then be evaluated by the other parts of the SUMPRODUCT formula without producing a #VALUE error.
__________

How's that?

Ok, so a smaller version of your data could be...

Total Hrs.....Late Hrs.......Attn
09:46.........00:13..........Late

With the formula being
=SUMPRODUCT((B$1:D$1="Attn")*(B2:D2="Late")*(A$4:C$4="Late Hrs")*IF(ISNUMBER(A5:C5),(A5:C5),0))

As you say, the data is in range A1:C2 but the first two arrays are populated from B1:D2, offset from the data by one cell to the right.
In this case we want to return the 00:13 value in cell B2, we know this because of three things:
1, it is in a column titled "Late Hrs"
2, it is one cell to the left of the word "late"
3, this word 'late' is in a column titled "Attn"

The sumproduct is evaluating these three things as such:
=SUMPRODUCT((3)*(2)*(1)*(value))

(B$1:D$1="Attn") carries out the following tests:
"Late Hrs"="Attn"
"Attn"="Attn"
""="Attn"

Which evaluates to:
FALSE
TRUE
FALSE

(B2:D2="Late") carries out the following tests:
"00:13"="Late"
"Late"="Late"
""="Late"

Which also evaluates to:
FALSE
TRUE
FALSE

(A$4:C$4="Late Hrs") carries out the following tests:
"Total Hrs"="Late Hrs"
"Late Hrs"="Late Hrs"
"Attn"="Late Hrs"

Which also evaluates to:
FALSE
TRUE
FALSE

Finally the last part returns the following values (the times will actually be decimal serial number, but don't worry about that for this):
09:46
00:13
0

So the whole thing evaluates as:
FALSE * FALSE * FALSE * 09:46
TRUE * TRUE * TRUE * 00:13
FALSE * FALSE * FALSE * 0

Which becomes:
0 * 0 * 0 * 09:46
1 * 1 * 1 * 00:13
0 * 0 * 0 * 0

Which becomes:
0
00:13
0

Which are added together to get the 00:13 you wanted.


Ok, so you see how all the TRUEs line up? If they didn't then you wouldn't get the right result. You need the second value of each to be TRUE. If we were to set up the first two tests in the same column range as the third and fourth then it would be the third value that would be true in each case. So shifting the range once cell to the right means we can perform the test on the cells one to the right of the value cell and have the array results 'align' to produce the right answer.


TL:DR
To validate a test on a cell one to the right you want to offset the validation range one to the right as well.