SUMPRODUCT & IFs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the sumproduct function to calculate totals on a summary sheet

For example

=SUMPRODUCT(--('S:\AdminDocuments\[Surrey.xls]ForCalculations'!$B$5:$B$564=$H$18),--('S:\AdminDocuments\[Surrey.xls]ForCalculations'!$C$5:$C$564=I20)

to calculate the number of entries that match criteria A and B and C. So far so good

Heres my problem:
On the next sheet i need to calculate the number of entries that match *any one* of four criterias. Its quite likely that an entry will match more than one criteria, but i don't want the entry to be counted more than once. How do i alter my formula to reflect this? Do i need to use an IF or an OR

Any suggestions greatly appreciated ;)
 
Hi
try something like
=SUMPRODUCT(--('S:\AdminDocuments\[Surrey.xls]ForCalculations'!$B$5:$B$
564={"crit1","crit2","crit3","crit4"}))
 
=SUMPRODUCT((condition1)+(condition2)+(condition3)+(condition4))
counts the number of occurrances where at least one condition is satisfied.
=SUMPRODUCT((condition1)*(condition2)*(condition3)*(condition4))
counts the number of occurrances where all 4 conditions are satisfied.

You can loose the leading -- in these forms, because the direct
operations will coerce the logical values to numbers.

Jerry
 
A pity that you didn't state for which those 4 criteria must hold and what
these criteria are. So what follows is a template:

Let X2:X4 house the 4 criteria...

=SUMPRODUCT(--ISNUMBER(MATCH(Range1,X2:X4,0)),--(Range2=Y2),Range3)

would sum any value from Range3 that corresponds to a Range2 cell for which
Y2 holds and a range 1 cell that matches X2:X4, the multiple criteria range.

The IsNumber/Match gives you the fastest way to OR multiple criteria that
must hold for the same range.

Katherine said:
I'm using the sumproduct function to calculate totals on a summary sheet.

For example:

=SUMPRODUCT(--('S:\AdminDocuments\[Surrey.xls]ForCalculations'!$B$5:$B$564=$
H$18),--('S:\AdminDocuments\[Surrey.xls]ForCalculations'!$C$5:$C$564=I20))

to calculate the number of entries that match criteria A and B and C. So far so good.

Heres my problem:
On the next sheet i need to calculate the number of entries that match
Click to expand...
*any one* of four criterias. Its quite likely that an entry will match more
than one criteria, but i don't want the entry to be counted more than once.
How do i alter my formula to reflect this? Do i need to use an IF or an OR?
 
Back
Top