# Thread: Excel Calc help needed (1.0)

1. ## Excel Calc help needed (1.0)

Can you help me derive the total for Column AI (Scheduled March) + Column AV (Estimated Effort) if Meg Month (Column O) = Mar or if In Progress stage (Column P) = Build, Deploy, Warranty, Obtain Authorization. The total should be place in Column AW (MEG MARCH). I have attached a sample to help. Any help you can offer is greatly appreciated.

I reall need a formula for deriving the total.

2. ## Re: Excel Calc help needed (1.0)

Could you attach a version that is not saved in XL2007. My computer can not read XL2007 files

Steve

3. ## Re: Excel Calc help needed (1.0)

No problem Steve. Reposted in .xls format

4. ## Re: Excel Calc help needed (1.0)

The array formula (confirm with ctrl-shift-enter) will give the sum of columns AI and AV when th value in O = "Mar" or the value in P is in the list.

=SUM(IF((\$O\$2:\$O\$10="Mar")+ISNUMBER(MATCH(\$P\$2:\$P\$ 10,{"Build","Deploy","Warranty","Obtain Authorization"},0)),\$AI\$2:\$AI\$10+\$AV\$2:\$AV\$10))

Since this is XL2007, you may be able to use the new SUMIFS formula as well...

Steve

5. ## Re: Excel Calc help needed (1.0)

Additionaly how can I rework the formula to update the AW column with column AV total when the status has a value of "approved" in column (N) and Column (O) has a value of March. The second condition that needs to be placed in the formula will update AW with the total of Sheduled March (AI) if no totals exist in column AV and column N has a value other than Approved but not blank. I also would like the counts in AW to be updated whenever modifications are made to AI or AV. The last formula did not adjust the counts when I made changes to the fields totals. Is thier anyway to make this happen??

6. ## Re: Excel Calc help needed (1.0)

I don't understand what you want the sum to do if "Approved" or some of your other conditions.

If Approved and Mar than it will still be included in the total since you asked for Mar OR something from that list. Anything with Mar will be added no matter what is in the other list.

The formula should update (do you have calculations on automatic?). the formula is a SUMMATION of all the cells matching the criteria. Are you after something that is more a "row based" subtotal (creating a column of intermediate values) and then sum them at the end? This formula does not do that.

Perhaps you should indicate what values you want in the various cells and explain the logic of the calculations and include examples of the various "types"/conditions.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•