Excel formula

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:31 PM GMT

Question

Word Doc

I am trying to write a formula that will return a total sum of one column but only if other columns criteria it ¡°TRUE¡±. I want the sum number to show up on one worksheet (1) while the data it is looking up pulls from another worksheet (2) (these two worksheets are all under the same workbook). For instance, please use the example below:


If the below is the Worksheet ¡°1¡± then I would want the formula result to be in cell B2 (I would drag the formula out to cell C2, D2 ect.). I would want the formula to react off whatever I put in cell B1, C1, D1 ect. Column A Column B Column C Column D Column 1 Week 21 22 22 Column 2 Count ¡°RESULT¡± ¡°RESULT¡± ¡°RESULT¡±

Therefore, whatever I type in the ¡°Week¡± row would make the formula look up the specific total values of ¡°Worksheet 2¡±

Worksheet 2 SizeType Week Count to be covered 40RH 20 2 40RH 21 2 40FF 21 3 40FF 22 4 40FR 22 5 40RH 22 3 40RH 20 6 40RH 20 3

So with the above data if I want the formula to find the total ¡°count to be covered¡± for week 20 with a sizetype of 40RH in essence the formula would only select those cells in the count to be covered column that meet the specific criteria of the Sizetype and Week column that are true to the above statement(week 20 with a sizetype of 40RH).

So if the formula is correct it should show the result as 11 for the sum of the below highlight that meet that criteria.

Worksheet 2 SizeType Week Count to be covered 40RH 20 2 40RH 21 2 40FF 21 3 40FF 22 4 40FR 22 5 40RH 22 3 40RH 20 6 40RH 20 3

Can anyone help me write this formula?

Answer

Assuming your table starts in cell A2 of Sheet2 (on Sheet2, cell A2 is 40RH; B2 is 20; C2 is 2; A3 is "40RH"; etc etc. Assuming you're using2007.

The formula to sum the values in col C of the table, for those rows in which col A is "40RH" and col B is 20, is:

=SUMIFS(Sheet2!C:C,Sheet2!A:A,"40RH",Sheet2!B:B,20)

Advertisement

©2021 eLuminary LLC. All rights reserved.