Combining formulas for sum

[Twyko]

Okay, this is a little bit complicated and convoluted but I will do my best to make it simple. Basically i have a lot of raw data that needs to be tallied up based on date and a direction tag. I'm trying to combine a COUNTIF formula with an IF statement as seen below

=IF(B9:B1000="10/4/10",COUNTIF($C$9:$C$1000,"inbound"),)

I only want the count to add if the range in Column B meets the date dictated in the string and has an entry of "inbound" in column C. Any ideas?

[Richard Buttrey]

Hi,

Unless you have Excel 2007 which has a new COUNTIFS() plural function, you'll need to add a helper column. So in D9 enter the formula

Please Login or Register  to view this content.

and copy this down as many rows as necessary. Now use the COUNTIF() function, i.e.

Please Login or Register  to view this content.

if the dates are proper date numbers and not text strings you'll need to modify that to

Please Login or Register  to view this content.

HTH

[Twyko]

And how would i accomplish this if i have 2007? I recently had the office upgrade me, so i'm at least working with somewhat current technology. Thanks for the help

[NBVC]

Try:

=COUNTIFS($B$9:$B$1000,"10/4/10",$C$9:$C$1000,"inbound")

[Twyko]

NBVC - I used the formula you provided and I'm coming up with a count of 0. The cells in column B also have time stamps with them, so they show up as follows:


10/4/2010 7:21
10/4/2010 8:08

Forgive my ignorance, but does that make a difference?


Essentially i'm attempting to get a count of how many incoming calls are made on a specific date.

Column B Column C
10/4/2010 7:21 inbound
10/4/2010 8:08 inbound
10/4/2010 8:38 outbound
10/4/2010 9:05 inbound
10/4/2010 9:08 outbound
10/4/2010 9:10 inbound
10/4/2010 9:13 inbound

so from that, i need a formula that will look at column B and only count the calls made on 10/4/10 that were inbound. Hopefully that clears up some ambiguity as to my aim with this.

[NBVC]

Yes, it makes a difference.. try:

=COUNTIFS($B$9:$B$1000,">=10/4/2010 12:00 AM",$B$9:$B$1000,"<=10/4/2010 11:59 PM",$C$9:$C$1000,"inbound")

[Twyko]

Thank you so much. Problem solved. It looks like this is exactly what i was looking for. A thousand "thank yous"