Count with multiple criteria

[Janne Jantunen]

Hello,

I'm trying to find a way to make a small "load" calculator which would count
the current load for each individual based on below data:

A B C D
JJ 4 21/04
JJ 2 19/04
BJ 2 18/04 DONE
BJ 5 16/04
JJ 2 20/04 DONE
BJ 3 24/04

Today : 21/04

A - Individual
B - Hour required to complete task
C - Deadline (week/year)
D - "DONE" if completed, otherwise empty

So the goal is to calculate for each indivual the total number of hours
(from column B) with following conditions:

for Individual JJ sum column B if C equal or is less than today AND if not
"done"

With this the result from above data would be:
JJ - 6
BJ - 5

I think this might be possible to do with array formulas, but it's way too
complex for me.
I'm living in Finland so our standard date representation is not dd/mm/yy
but dd.mm.yy ... would this cause a problem when comparing deadline to today
(both would be manually inputed text)?

Any help would be greatly appreciated.

- Janne -

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(C1:C100<=TODAY()),--(A1:A100="JJ"),--(D1100<>"DONE"),B
1:B100)

 

[Janne Jantunen]

Hello Frank,

Thanks for a very quick reply.
Your formula works very well. However I couldn't get it to work exactly like
that as the comparison between DEADLINE and TODAY() didn't work (as DEADLINE
is not a date format), but with very minor tweaks it is now working and
making me warm and happy all over

Bye the way, I don't understand your formula completely. What is the meaning
of "--" in the formula. Office help didn't explain that.

Again thanks alot.

-Janne-

 

[Frank Kabel]

Hi
the '--' coerces the boolean values to real numbers (TRUE=1/FALSE=0)