Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional formula - based on 2 separate criteria
how to set up SUMIF conditional formula - based on 2 separate criteria - over
hte same range and sum_range ? |
#2
|
|||
|
|||
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" wrote in message ... how to set up SUMIF conditional formula - based on 2 separate criteria - over hte same range and sum_range ? |
#3
|
|||
|
|||
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, itd be =SUMIF(A2:A30,A39,E2:E30). But I cant 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#4
|
|||
|
|||
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 "andrewo-s" wrote in message ... 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#5
|
|||
|
|||
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) "andrewo-s" wrote in message ... 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#6
|
|||
|
|||
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" wrote in message ... 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) "andrewo-s" wrote in message ... 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#7
|
|||
|
|||
Ł, $, it's all currency :-)
Seriously, thanks for that. Bob "JulieD" wrote in message ... 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" wrote in message ... 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) "andrewo-s" wrote in message ... 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#8
|
|||
|
|||
:-)
"Bob Phillips" wrote in message ... Ł, $, it's all currency :-) Seriously, thanks for that. Bob "JulieD" wrote in message ... 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" wrote in message ... 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) "andrewo-s" wrote in message ... 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 D35:D39 referring to area code DEH. Any further suggestions ? |
#9
|
|||
|
|||
Thanks to both Bob & Julie - problem sorted.
Just have one follow up question ... what do the " - - " bits actually do ? |
#10
|
|||
|
|||
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation
-- HTH RP (remove nothere from the email address if mailing direct) "andrewo-s" wrote in message ... Thanks to both Bob & Julie - problem sorted. Just have one follow up question ... what do the " - - " bits actually do ? |
#11
|
|||
|
|||
Thanks for the webpage link - will remember it for next time - and thanks for
giving me the short summary explanation first ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula based on criteria. | New Users to Excel | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Conditional Formatting based on Formula | Excel Worksheet Functions | |||
How do I do conditional format based on a cell with a formula? | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |