conditional formula - based on 2 separate criteria

[andrewo-s]

how to set up SUMIF conditional formula - based on 2 separate criteria - over
hte same range and sum_range ?

 

[Bob Phillips]

You could use something like

=SUMPRODUCT(--(rng1="value1"),--(rng2="value2"),rng2)<100

This will check the first range for a value, and a second range for a
second value, summing the second range, and CFing when that value < 100
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[andrewo-s]

Thanks for the suggestion ... I tried SUMPRODUCT ... but I think it's not
what I'm after.

I don't think my previous question was specific enough. By way of example:
Column A = one of 3 area codes (e.g. "PT", "GR"& "SK")
Column B = one of 5 account codes (e.g. "301", "302"...)
Column C&D = unit cost & quantity per line item
Column E = total cost per line item

Lets say there are 29 rows of data (rows 2-30)

What I want to do is make one summary table where for each area code the
table sums the total for each of the 5 account codes. i.e. there are two
criteria: area code and account code.

If there is just one criteria (e.g. account code) then I know how to sum
each account code into a summary table by using the formula =SUMIF(range1,
criteria1, range 2) where in this case range1 is A2:A30 and range 2 is the
corresponding E2:E30 – and where criteria 1 is also a range. eg in cells
A35:A39 I put in the 5 account codes “301â€â€¦ then I copy the above formula
from cells B35 to B39 – where the formula in cell B35 would be
=SUMIF(A2:A30,A35,E2:E30) and the final cell, B39, it’d be
=SUMIF(A2:A30,A39,E2:E30).

But I can’t work out how to refer to two criteria at the same tame.

What I want to do is set up 3 columns in the summary table – B35:B39
referring to area code PST, C35:C39 referring to area code LTR and D3539
referring to area code DEH.

Any further suggestions ?

 

[JulieD]

Hi

you've managed to confuse me .. where did PST, LTR and DEH come from

personally, i would create a pivot table, and drag the area codes to the row
section, the account codes to the column section and the total cost to the
data section ...

but if you want a table, then use SUMPRODUCT

-with area code headings in row 34
the formula for B35 is
=SUMPRODUCT(--($A$1:$A$30=B$34),--($B$1:$B$30=$A35),$E$1:$E$30)
fill down and across

Hope this helps
Cheers
JulieD

 

[Bob Phillips]

As before

Setup thes cells

B34: PST
C34: LTR
D34: DEH
A35: 301
A36: 302
A37: 303
A38: 304
A39: 305

In B35, add

=SUMPRODUCT(--($A$2:$A$30=B$35),--($B£2:$B$30=$A35),$E$2:$E$30)

and copy across and down to D39

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JulieD]

typos corrected

=SUMPRODUCT(--($A$2:$A$30=B$35),--($B£2:$B$30=$A35),$E$2:$E$30)

should be

=SUMPRODUCT(--($A$2:$A$30=B$34),--($B$2:$B$30=$A35),$E$2:$E$30)

Cheers
JulieD

 

[Bob Phillips]

£, $, it's all currency

Seriously, thanks for that.

Bob

 

[JulieD]

£, $, it's all currency

Seriously, thanks for that.

Bob

 

[andrewo-s]

Thanks to both Bob & Julie - problem sorted.

Just have one follow up question ... what do the " - - " bits actually do ?

 

[Bob Phillips]

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

Thanks to both Bob & Julie - problem sorted.

Just have one follow up question ... what do the " - - " bits actually do

?

 

[andrewo-s]

Thanks for the webpage link - will remember it for next time - and thanks for
giving me the short summary explanation first !