Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if formula using multiple criteria
I am trying to create a sum if formula that takes the given in Rows B and
places them in D given the Criteria in C matches that of A under the Payable, Billable or Burden in B. D1 is manual typed as Billable, payable or burden. Thank you A B C D 1 Payable PAYABLE 2 M 10 M 10 3 S 20 D 20 4 D 30 S 30 5 6 7 Billable 8 M 15 9 S 25 10 D 35 11 12 Burden 13 M 6 14 S 12 15 D 18 16 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if formula using multiple criteria
Not sure I understand but when someone asks how to sum subject to multiple
criteria, one always thinks of SUMPRODUCT For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeremy" wrote in message ... I am trying to create a sum if formula that takes the given in Rows B and places them in D given the Criteria in C matches that of A under the Payable, Billable or Burden in B. D1 is manual typed as Billable, payable or burden. Thank you A B C D 1 Payable PAYABLE 2 M 10 M 10 3 S 20 D 20 4 D 30 S 30 5 6 7 Billable 8 M 15 9 S 25 10 D 35 11 12 Burden 13 M 6 14 S 12 15 D 18 16 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if formula using multiple criteria
Didn't tell me what I was looking for. Thanks for the try.
"Bernard Liengme" wrote: Not sure I understand but when someone asks how to sum subject to multiple criteria, one always thinks of SUMPRODUCT For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeremy" wrote in message ... I am trying to create a sum if formula that takes the given in Rows B and places them in D given the Criteria in C matches that of A under the Payable, Billable or Burden in B. D1 is manual typed as Billable, payable or burden. Thank you A B C D 1 Payable PAYABLE 2 M 10 M 10 3 S 20 D 20 4 D 30 S 30 5 6 7 Billable 8 M 15 9 S 25 10 D 35 11 12 Burden 13 M 6 14 S 12 15 D 18 16 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if formula using multiple criteria
In D2:
=SUMPRODUCT(--(OFFSET(B$1,MATCH(D$1,B:B,0),-1,3)=C2), OFFSET(B$1,MATCH(D$1,B:B,0),,3)) Copy D2 down to D4 to return results as desired, which are flexible to/dependent on the input in D2 (Payable, Billable, or Burden), as well as the inputs in C2:C4 (M, D, or S). Success? High-five it here, click the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Jeremy" wrote in message ... I am trying to create a sum if formula that takes the given in Rows B and places them in D given the Criteria in C matches that of A under the Payable, Billable or Burden in B. D1 is manual typed as Billable, payable or burden. Thank you A B C D 1 Payable PAYABLE 2 M 10 M 10 3 S 20 D 20 4 D 30 S 30 5 6 7 Billable 8 M 15 9 S 25 10 D 35 11 12 Burden 13 M 6 14 S 12 15 D 18 16 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if formula using multiple criteria
Slight typo he
.. dependent on the input in D2 (Payable, Billable, or Burden), Should read as: .. dependent on the input in D1 (Payable, Billable, or Burden), -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria formula | Excel Discussion (Misc queries) | |||
Need help with a multiple criteria formula | Excel Worksheet Functions | |||
Sum formula for multiple criteria... help? | Excel Discussion (Misc queries) | |||
Formula to sum multiple columns on multiple criteria | Excel Discussion (Misc queries) | |||
How do I set up a formula with multiple criteria? | Excel Worksheet Functions |