I am trying to calculate the total time in one cell, if another cell equals a
certain test.
Example:

A B C D
1 :15 1S
2 1:00 2S
3 :45 3I
4 2:00 1S

So i have a countif formula that counts cell 1C, but where i am having
problems is if 1C equals 1S, then i want it to add cell 1B. And then in that
same cell, it would also see cell C4 and add cell B4 to it.
I hope this make some sense.
Any help would be great!
Thanks
Rob

Maybe...

=sumif(c:c,"1s",b:b)
or
=sumif(c:c,c1,b:b)

You may want to look at pivottables. You can get nice summary reports like this
pretty easily.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Rob wrote:

I am trying to calculate the total time in one cell, if another cell equals a
certain test.
Example:

A B C D
1 :15 1S
2 1:00 2S
3 :45 3I
4 2:00 1S

So i have a countif formula that counts cell 1C, but where i am having
problems is if 1C equals 1S, then i want it to add cell 1B. And then in that
same cell, it would also see cell C4 and add cell B4 to it.
I hope this make some sense.
Any help would be great!
Thanks
Rob

--

Dave Peterson

Instead of counting, you want to sum.

=SUMIF(C:C,"1S",B:B)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rob" wrote:

I am trying to calculate the total time in one cell, if another cell equals a
certain test.
Example:

A B C D
1 :15 1S
2 1:00 2S
3 :45 3I
4 2:00 1S

So i have a countif formula that counts cell 1C, but where i am having
problems is if 1C equals 1S, then i want it to add cell 1B. And then in that
same cell, it would also see cell C4 and add cell B4 to it.
I hope this make some sense.
Any help would be great!
Thanks
Rob

Try =SUMPRODUCT(--(C1:C4="1S");(B1:B4)) and see if it is what you need

"Rob" wrote:

I am trying to calculate the total time in one cell, if another cell equals a
certain test.
Example:

A B C D
1 :15 1S
2 1:00 2S
3 :45 3I
4 2:00 1S

So i have a countif formula that counts cell 1C, but where i am having
problems is if 1C equals 1S, then i want it to add cell 1B. And then in that
same cell, it would also see cell C4 and add cell B4 to it.
I hope this make some sense.
Any help would be great!
Thanks
Rob

Paste the below formula in D2 cell
=IF(B2="","",IF(LEFT(B2,1)=":",TIME(0,RIGHT(B2,2), 0),VALUE(B2)))
Place the cursor in D Column cell and Select the D Column by hitting
Cntrl+Spacebar and do Right ClickFormat
CellsNumberCategoryTimeType Select 13:30 and give Ok.
Now copy the D2 cell and paste it to the remaining cells of D Column depends
upon the B Column data.

Paste the below formula in E2 cell
=IF(C2="","",SUMIF(C:C,C2,D:D))
Place the cursor in E Column cell and Select the E Column by hitting
Cntrl+Spacebar and do Right ClickFormat
CellsNumberCategoryTimeType and paste the below format
[h]:mm
and give Ok.
Now copy the E2 cell and paste it to the remaining cells of E Column depends
upon the C Column data.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Rob" wrote:

I am trying to calculate the total time in one cell, if another cell equals a
certain test.
Example:

A B C D
1 :15 1S
2 1:00 2S
3 :45 3I
4 2:00 1S

So i have a countif formula that counts cell 1C, but where i am having
problems is if 1C equals 1S, then i want it to add cell 1B. And then in that
same cell, it would also see cell C4 and add cell B4 to it.
I hope this make some sense.
Any help would be great!
Thanks
Rob