Sumproduct and Date again...
Dear Experts,
this topic drives my crazy :-(
Date is a real challange in excel with all the different options and cases...
I have to check for several parameters including a date option which does not work as I expect it to work.
See attached file!
I want to find out how many people work in the Dept. JIM in Jan-12....later on I will import "fresh" data and I do not want to loose results for Jan-12 and on top get result for Feb and so on...
My idea:
As long an employee has "none" in the dismissal column it is clear that the person is working.
Like in the sample....up to Oct-11 we had 16 people working there.
With the help of my formula I did expect to have in my monthly updated table in the cell Oct-11 = 16 and the next cell Nov-11 would be 15 and of course in the cell jan-12 should be also 15....
what is wrong?
thanks for help
Sample.xlsx
this topic drives my crazy :-(
Date is a real challange in excel with all the different options and cases...
I have to check for several parameters including a date option which does not work as I expect it to work.
See attached file!
I want to find out how many people work in the Dept. JIM in Jan-12....later on I will import "fresh" data and I do not want to loose results for Jan-12 and on top get result for Feb and so on...
My idea:
As long an employee has "none" in the dismissal column it is clear that the person is working.
Like in the sample....up to Oct-11 we had 16 people working there.
With the help of my formula I did expect to have in my monthly updated table in the cell Oct-11 = 16 and the next cell Nov-11 would be 15 and of course in the cell jan-12 should be also 15....
what is wrong?
thanks for help
Sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I saw that Barry, but got called away to a meeting, so couldn't finish, thanks for intervening!
I had locked on to the adding bit, but hadn't quite finished, although I admit that my opening gambit was slightly different from yours above.
I had locked on to the adding bit, but hadn't quite finished, although I admit that my opening gambit was slightly different from yours above.
ASKER
Hi,
please could you explain the need of the >0 at the very end?
It works! Thanks but I try to understand why....first part clear, "None" part clear, + >G3 also clear but why >0?
thanks
please could you explain the need of the >0 at the very end?
It works! Thanks but I try to understand why....first part clear, "None" part clear, + >G3 also clear but why >0?
thanks
That's a generic way to handle ORs that are not mutually exclusive.
If you have a mutually exclusive situation, e.g. you want to count how many rows have "x" in column A and "y" or "z" in column B then, clearly B can't contain "y" and "z" simultaneously (as entire cell contents) so you can use this
=SUMPRODUCT((A2:A10="x")*(
This part
(B2:B10="y")+(B2:B10="z")
returns an array of values....and because at most 1 condition can be satisfied they will be either 1 or 0
...but in your case because any text value is deemed to be greater than any number in Excel then, effectively your two conditions are not mutually exclusive (when "None" is in A2:A17 it is both "None", obviously, and >G3) so you need to make sure you don't count that twice), i.e. this part in my suggested formula
(A2:A17="None")+(A2:A17>=G
with your example returns this array
{2;2;2;2;2;2;2;2;0;2;2;2;2
the 2s meaning that both conditions are satisfied
If we use that "as is" the the result will be wrong so we just want to count values in that array that are >0, hence >0 :)
regards, barry
If you have a mutually exclusive situation, e.g. you want to count how many rows have "x" in column A and "y" or "z" in column B then, clearly B can't contain "y" and "z" simultaneously (as entire cell contents) so you can use this
=SUMPRODUCT((A2:A10="x")*(
This part
(B2:B10="y")+(B2:B10="z")
returns an array of values....and because at most 1 condition can be satisfied they will be either 1 or 0
...but in your case because any text value is deemed to be greater than any number in Excel then, effectively your two conditions are not mutually exclusive (when "None" is in A2:A17 it is both "None", obviously, and >G3) so you need to make sure you don't count that twice), i.e. this part in my suggested formula
(A2:A17="None")+(A2:A17>=G
with your example returns this array
{2;2;2;2;2;2;2;2;0;2;2;2;2
the 2s meaning that both conditions are satisfied
If we use that "as is" the the result will be wrong so we just want to count values in that array that are >0, hence >0 :)
regards, barry
You need to test the results separately.
=SUMPRODUCT((B2:B17=F3)*(A