Multiple Condition Sum

  • Thread starter Thread starter mkaake
  • Start date Start date
M

mkaake

I've seen this asked a lot, but the answers have never really made sens
to me... So here it goes - my feeble attempt to understand what i
going on...

Here's the setup: I've got data that users are going to input, whic
tells me a tally of the defects that they have for a certain day. I
column a, they enter the date. Column B is for the shift. Column C an
up are named as p1p-p(whatevernumber)p (for easy lookup - that way whe
I am looking for a specific defect, I can have a drop down list, whic
will reference a number, which with a concatenate, i can turn into th
named range).
I'm trying to find a new and faster way of looking up defects by da
and shift, because the current system is a dog.
What I normally do, is have (several) columns in addition to the dat
entry that have various if statements (ie if date>selectedate, 1, 0
and then use sumif statements on those columns to find values. I woul
really like to get away from this, because it makes me limit the shee
to how many rows I believe will be used (because the formula needs t
be copied for every possible entry).

So visually, here's what I need to do:

_ A [date] __ B [shift] _ C [p1p] D [p2p] E [p3p]
1 4/5/2004 _ 1 ______ 1 ______ 5
2 4/3/2004 _ 1 ______ 2 ______ 3
3 4/3/2004 _ 2 ______ 6 ______ 2
4 4/2/2004 _ 1 ______ 0 ______ 1
5 4/2/2004 _ 2 ______ 5 ______ 3



etc. So I'm looking for a forumla that will let me say: sum if date i
[dateselected] (named range) and shift is [shiftselected], then su
[p1p].

If that's possible, than can it be expanded to if the date is betwee
two dates?

Anyhew, sorry if you guys have answered this before - I've just neve
quite been able to understand what the answers meant! :rolleyes:

matt

<edited because extra spaces were taken out... like a TRIM function ;)
 
A little editing left me with:

=SUMPRODUCT(--(Date=dateselect),--(Shift=shiftselect), p1p)

which seems to work out quite nicely. and i think i might actuall
understand what it's doing - am i right with this thinking:

the -- looks at the condition (date=dateselect), and if it's true, i
makes it a 1, if it's false, it makes it a zero. then it multiplies i
by the second array, which is the same setup. so if it meets thos
criteria, we're looking at 1*1. Finally, it multiplies by the las
column, which leaves us with the actual number we wanted to look up
the value of p1p.

Pretty nice trick there... is there any way to change it (i'll b
playing with it in a minute here) so that you can lookup a range o
dates, so it's something to the effect of --(date>dateselect)
--(date<=dateselect2), --(shift=shiftselect), p1p)? (assumin
dateselect2 is some other date)

Well in any event, thank you very much!

mat
 
Hi
see below
A little editing left me with:

=SUMPRODUCT(--(Date=dateselect),--(Shift=shiftselect), p1p)

which seems to work out quite nicely. and i think i might actually
understand what it's doing - am i right with this thinking:

the -- looks at the condition (date=dateselect), and if it's true, it
makes it a 1, if it's false, it makes it a zero. then it multiplies it
by the second array, which is the same setup. so if it meets those
criteria, we're looking at 1*1. Finally, it multiplies by the last
column, which leaves us with the actual number we wanted to look up -
the value of p1p.
Click to expand...

Perfect!
Just as an addition: You could use any mathematical operation to coerce
the boolean value into a number (e.g. 1* or 0+) but the unary operator
has some benefits:
- a little bit faster than multiplying the matrices
- operator preference by Excel (-- has a higher priority)


Pretty nice trick there... is there any way to change it (i'll be
playing with it in a minute here) so that you can lookup a range of
dates, so it's something to the effect of --(date>dateselect),
--(date<=dateselect2), --(shift=shiftselect), p1p)? (assuming
dateselect2 is some other date)
Click to expand...

Also correct
 
Update:

I fiddled with the product sum function, and while it works, it's a
*dog* on speed (used for 100 different cells), so I'm trying a new
approach - back to the columns with values, and doing sumifs based on
that. my followup question is what would be faster?
using an if statement of this effect:
=if(date>=end1, if(date<=week1, if(shift=shiftselect, 1, 0), 0), 0)

or

=--(date>=end1)*--(date<=week1)*--(shift=shiftselect)

I have a hunch that even though the bottom function is simpler, the if
statement might be faster, because it will stop trying to figure out
what's going on (as far as multiplying numbers) as soon as one of the
IF's fails - and most of them in my worksheet (decided to limit the
user to 2500 inputs) will fail the first condition, and all but a
handful will fail the second...

thoughts?

matt
 
Back
Top