Is this a job for SUMPRODUCT? Or is it easier?

[Saladsamurai]

I have done a lot of reading on the SUMPRODUCT as a Countif formula.

However, my ranges are not both columns; so I do not know if they will be multiplicatively conformable.

Here is the task:

I have the following:

WorkSheet named "X"
WorkSheet named "Y"
WorkSheet named "Z"

If the i-th Row in Column B in WorkSheet "X" is a "T" then I would like to Count all of the cells in the i-th row of "Y" that are <= some cell in "Z". Let's call it 'Z'!A1 for now.


After typing this out, I get the feeling that it might be easier than I am making it.:help

[Bob Phillips]

Try


=IF(X!Bi="T",COUNTIF(Y!i:i,"<="&Z!A1),"")

[Saladsamurai]

I have not tried that yet xld. But, I think I left out something important that I am not sure how to convey.

I want to apply this to a fixed RANGE. My goal is to start categorizing the data.

I have attached a sample that uses 2 countifs as a way to convey the desired results.

Check out Sheet "Z" first.

[Bob Phillips]

=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))

[Saladsamurai]

Wow. Just Wow...

xld, can you explain to me some of the details of this? I understand the whole true=1 and false=0. I am fine with that.

But, what are these cell references ? I have seen $B$1 before (absolute ref).

But what is this: Y!$1:$3 ? ---->sheet "Y"--->?--->?

EDIT: Got it I think. Rows 1 - 3.

[Bob Phillips]

Y!$1:$3 is just rows 1 through 3 on sheet Y, I assume your real sheet will have more rows.

[Saladsamurai]

Yes it does. Hmm. I amstill trying to understand this, so that I can change it as needed.

So, in this case, the function SUMPRODUCT only has ONE argument right?

And that argument is just whatever the result of


(ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T")

is,correct?

If so, I have 2 more quick questions. But I will wait first.

[Bob Phillips]

I am not sure what you mean by it just has one argument.

It has 3 conditions

(ISNUMBER(Y!$1:$3)) - only include numeric values in rows 1:3, i.e. not blanks

(Y!$1:$3<=Z!A1) - only include values in rows 1:3 <= A1 on Z

(X!$B$1:$B$3="T") - only include rows where the corresponding value in column B of X is a T

The three conditions are ANDed (*), to give an array of 1s and 0s which are summed.

[Saladsamurai]

Right. So you are basically multiplying 3 arrays, each of which resulted from each of the 3 conditions.

I guess I am just wondering why you could not just replace SUMPRODUCT with SUM, since you are really only 'feeding' it 1 array?

[Bob Phillips]

You aren't really feeding it one array, you are ANDing 3 arrays. This


=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1))

is the same as this


=SUMPRODUCT(--(ISNUMBER(Y!$1:$3)),--(Y!$1:$3<=Z!A1))

but you cannot use the latter form when you introduce the 3rd condition, as it is a different shape than the other two.

And yes, you could use SUM but you would have to array enter it. But that is true of most if not all SUMPRODUCT solutions.

[Saladsamurai]

Ok. I see. I will need to find some documentation on what exactly 'ANDing' means.

So, out of curiousity: Do you know how it does handle the 3rd array? Does 'ANDing' mean that Excel somehow detects the 3rd Array's shape and reshapes it to be compatible?

EDIT: I kind of see what is happening now.

The size of the Array: (ISNUMBER(Y!$1:$3)

is equal to the size of

(Y!$1:$3<=Z!A1)

So it ANDs them. Then the 3rd is an absolute array reference that results in either True=1 or False=0.

Then I get lost.....but it is becoming clearer.

[Bob Phillips]

Ok. I see. I will need to find some documentation on what exactly 'ANDing' means.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.


So, out of curiousity: Do you know how it does handle the 3rd array? Does 'ANDing' mean that Excel somehow detects the 3rd Array's shape and reshapes it to be compatible?

Excel doesn't detect the shape, you told it when you stated the ranges to test. Because it is multiplying the arrays, when it sees a horizontal range/array and a vertical range/array, it creates a matrix array result, which gets summed as per normal. That is why you cannot use the double unary form, because that is using SUMPRODUCT in its standard form, which will not multiply the different shaped arrays.

[Saladsamurai]

Ok thanks! Also, I just wanted to check. When I use this

=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))

and I drag the fill handle downwards. Each count also includes those from the count preceding it right?

That is:

In order to find the count in a particular class, I would have to subtract the count from the one before it.

I think I have answered by own question.

So, I presume that if I want the total of ALL cells in "Y" who have a corresponding "T" in "X" in their respective row designations I would leave off the middle condition.

So
Total =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(X!$B$1:$B$3="T"))

[Bob Phillips]

Ok thanks! Also, I just wanted to check. When I use this
=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))

and I drag the fill handle downwards. Each count also includes those from the count preceding it right?

That is:

In order to find the count in a particular class, I would have to subtract the count from the one before it.

I think I have answered by own question.

I cannot see wht you would copy down, you only need a single count.


So, I presume that if I want the total of ALL cells in "Y" who have a corresponding "T" in "X" in their respective row designations I would leave off the middle condition.

So Total =SUMPRODUCT((ISNUMBER(Y!$1:$3))*(X!$B$1:$B$3="T"))

I would use SUMIF here.

[Saladsamurai]

I cannot see wht you would copy down, you only need a single count.


No. That Counts all those with values <=-100%

I also need those
<=-90%
<=-80%
<=-70%

All the way through +100 %

So inorder to get those between -100% and -90% I would need to calculate

(Those <=-90%) - (Those <=-90%)

You dig?


I would use SUMIF here.

Ok. I had already used SUMPRODUCT before you posted this and it works. Is there a performance penalty?

Actually, I don't see how to use SUMIF here. The range sizes are not equal.

How do I tell it I want the total of all cells in 'Y' whose row number = row number in 'X' that contains "T" ?

[Bob Phillips]

You are right, you cannot use SUMIF here, but where possible SUIF should be used, there is a large performance hit using SUMPRODUCT, or any array formula.

[Saladsamurai]

Ok great. Thank you so much for all of your help xld! I have another, what I hope is a 'quick' question.

I would like to modify the condition:


=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))

such that instead of = "T"

it could be

="C" OR ="K"

with the 1st two conditions still the same.

The 1st two conditions are still the same though.



And while I am here, I would need another one such that instead of

= "T"

it must be

="C" AND ="K"

[Bob Phillips]

=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(X!$B$1:$B$3="T"))

such that instead of = "T"

it could be

="C" OR ="K"

with the 1st two conditions still the same.

=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(ISNUMBER(MATCH(X!$B$1:$B$3,{"C","K"},0))))


And while I am here, I would need another one such that instead of

= "T"

it must be

="C" AND ="K"

it can't be C and K, if it is C it is NOT K and vice versa.

[Saladsamurai]

=SUMPRODUCT((ISNUMBER(Y!$1:$3))*(Y!$1:$3<=Z!A1)*(ISNUMBER(MATCH(X!$B$1:$B$3,{"C","K"},0))))

OK. I used (Sheet Names Changed, Sorry)


=SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")+('Cooling Type'!$A$1:$A$4="K")))

and it seems to work. Sorry..."C" or "K" would be found in column "A" not "B"


it can't be C and K, if it is C it is NOT K and vice versa

Oops. I meant (Column A is a "C" or "K") AND (Column B is a "T")

for which I used


=SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T")))

which also works fine.

Not pretty, but they work.

[Bob Phillips]

OK. I used (Sheet Names Changed, Sorry)


=SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")+('Cooling Type'!$A$1:$A$4="K")))

and it seems to work. Sorry..."C" or "K" would be found in column "A" not "B"

Mine is neater! But that is sophisticated SP for someone who supposedly doesn't understand it.



Oops. I meant (Column A is a "C" or "K") AND (Column B is a "T")

for which I used


=SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)*(('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T")))

which also works fine.

Dont' you mean


=SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$IV$4))*('Cold Difference'!$A$1:$IV$4<=Charts!A1)
*(ISNUMBER(MATCH('Cooling Type'!$A$1:$A$4,{"C","K"},0)))*('Cooling Type'!$B$1:$B$4="T"))

[Saladsamurai]

Dont' you mean


=SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$IV$4))*('Cold Difference'!$A$1:$IV$4<=Charts!A1)
*(ISNUMBER(MATCH('Cooling Type'!$A$1:$A$4,{"C","K"},0)))*('Cooling Type'!$B$1:$B$4="T"))

I am not sure what you are doing with the 'MATCH' as I have never used it. DO I need it? (presuming I don't care about neatness for now)

I am not sure what part of my


=SUMPRODUCT((ISNUMBER( 'Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)* ...
... (('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T")))


in particular you are correcting? Could you maybe boldface it?



I have the condition:
(ISNUMBER( 'Cold Difference'!$1:$4))

ANDed with the condition


('Cold Difference'!$1:$4<=Charts!A1)

ANDed with the resultant of the two conditions


(('Cooling Type'!$A$1:$A$4="C")*('Cooling Type'!$B$1:$B$4="T"))

Updated sample file attached. You can see that on the "Charts" sheet, everything works out.

[Bob Phillips]

You said it was C or K, but you are only testing for C, so I changed that.

[Saladsamurai]

You said it was C or K, but you are only testing for C, so I changed that.

Ahh yes.... It should test for


((Isnumber)*( <= some number)) AND ((=C or =K) AND ( = T))

So if I were to use my "super messy version" it would be:



=SUMPRODUCT((ISNUMBER( 'Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)* _
((( 'Cooling Type'!$A$1:$A$4="C") + ( 'Cooling Type'!$A$1:$A$4="K"))* _

('Cooling Type'!$B$1:$B$4="T")))


Without all the whitespace obviously. Just trying to make it legible.

Neatness aside, that works right?

P.S. Why did you use the 2nd 'ISNUMBER' function? How does that work? It appears that
you are testing to see if there is a number, but there is not....there is a "C" or a "K"....

P.P.S. I presume this was a typo right?


=SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$IV$4))*('Cold Difference'!$A$1:$IV$4<=Charts!A1)
*(ISNUMBER(MATCH('Cooling Type'!$A$1:$A$4,{"C","K"},0)))*('Cooling Type'!$B$1:$B$4="T"))

[Bob Phillips]

Ahh yes.... It should test for


((Isnumber)*( <= some number)) AND ((=C or =K) AND ( = T))

So if I were to use my "super messy version" it would be:




=SUMPRODUCT((ISNUMBER( 'Cold Difference'!$1:$4))*('Cold Difference'!$1:$4<=Charts!A1)* _

((( 'Cooling Type'!$A$1:$A$4="C") + ( 'Cooling Type'!$A$1:$A$4="K"))* _

('Cooling Type'!$B$1:$B$4="T")))



Without all the whitespace obviously. Just trying to make it legible.

Neatness aside, that works right?

As far as I can see, yes that should be the same.


P.S. Why did you use the 2nd 'ISNUMBER' function? How does that work? It appears that
you are testing to see if there is a number, but there is not....there is a "C" or a "K"....

True, C and K are not numbers, but when you MATCH a range against an array, an arry of numbers or lettes, you get a returned array of numbers (where a match is found), or #N/A! (where no match is found), and ISNUMBER is used to check for matches.



P.P.S. I presume this was a typo right?


=SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$IV$4))*('Cold Difference'!$A$1:$IV$4<=Charts!A1)
*(ISNUMBER(MATCH('Cooling Type'!$A$1:$A$4,{"C","K"},0)))*('Cooling Type'!$B$1:$B$4="T"))


Looks like it, I don't know how that crept in. I probably tried in in Excel 2007!

[Saladsamurai]

Sweet Jesus! Thanks again xld! I feel like I really learned something here...not just copy and pasting.

Still have a lot of questions about SUMPRODUCT, very basic ones.

Perhaps I will start a new thread later and list them. They are quite fundamental, so I will read the link you gave me in an attempt to answer them myself.

Thanks again! :friends:

[Bob Phillips]

If they are SP specific, please address them to thsi specialist sub-foruum.