Extremely Complex If Statement 1

[RodS2]

I am having a hard time creating an if statement that lets me total up the items within a column. Here is my data:

SPREADSHEET TAB #1
Task Estimate (in minutes Due Today? Project
XYZ 15 Yes 2
ABC 30 No 1
123 60 Yes 2


Here is where I am having the problem. In a second tab, I want to add up the total estimate (in minutes) if an item is due today based on the project. So if Project #2 has 3 tasks due that day, I want it to add up those 3 tasks that are due and give me the total time I estimated. So from the example above, project #2 has two tasks Due Today for a total of 75 minutes.

Here is how the second spreadsheet is set-up

SPREADHSEET TAB #2

Project Total Due Today (in minutes)
1 <FORMULA TO AUTOMATICALLY SUM HERE>
2 <FORMULA TO AUTOMATICALLY SUM HERE>


I have done a number of IF tasks, but it won't let me search an entire column for a "Yes" in due today, it wants to make me go cell by cell, put it in a seperate spreadsheet and do it that way, but there has to be a better more efficient way. Thanks for your help

 

[macropod]

Hi Rod,

All this requires is a simple SUMPRODUCT formula:

=SUMPRODUCT((D1:D10=2)*(C1:C10="Yes"),B1:B10)

or an array formula:

=SUM(IF((D1:D10=2)*(C1:C10="Yes"),B1:B10))

Array formulae are entered with Ctrl-Shift Enter, instead of just pressing the Enter key. You can replace the '2' and '"Yes"' in the formula with cell references if you like.

Naturally, you'll need to add the sheet references. Hint: You can simplify this process if you input the formula on the data sheet then cut & paste it to the output sheet.

Cheers

[MS MVP - Word]

 

[SkipVought]

RodS2,

Please do a FIND on this page, for TGML and use for your columnar examples, so that columns allign
[tt]
SPREADSHEET TAB #1
Task Estimate (in minutes Due Today? Project
XYZ 15 Yes 2
ABC 30 No 1
123 60 Yes 2
[/tt]

Chack out the SUMPRODUCT fumction. Using named ranges...
[tt]
=sumproduct((Project=A2)*(Due_Today="Yes")*(Estimate__in_minutes))
[/tt]


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RodS2]

Great! thank you both, it worked!!