Convert COUNTIFS to Sum Product?

[Jhpirate]

So I have been try for the past hour or so to get this formula working but this is the best I could come up with
=COUNTIFS('PASTE HERE'!$D$2:$D$1000000,A11,'PASTE HERE'!$J$2:$J$1000000,"Outbound",'PASTE HERE'!$I$2:$I$1000000,">0")
is there any way to convert this to a sum-product or something so it can actually detect if the values are greater than 0.
Any help would be greatly appreciated!

 

[Jonmo1]

That looks correct..

It will count rows where D=A11 AND J="Outbound" AND I>0

Are you sure it's the >0 part that is not working?
Seperate the functions to verify, what do these show?

=COUNTIFS('PASTE HERE'!$D$2:$D$1000000,A11,'PASTE HERE'!$J$2:$J$1000000,"Outbound")
And
=COUNTIFS('PASTE HERE'!$I$2:$I$1000000,">0")


Perhaps the values in I are not really numeric, they could be numbers stored as text
What does this return
=ISNUMBER('PASTE HERE'!$I2) - Fill down.

 

[Scott Huish]

Your formula worked just fine for me. Can you give an example of what didn't work? Have you verified that all of your criteria are met?

Excel 2010
	A	B
11	12	1
Sheet1

Cell Formulas
Range	Formula
B11	=COUNTIFS('PASTE HERE'!$D$2:$D$1000000,A11,'PASTE HERE'!$J$2:$J$1000000,"Outbound",'PASTE HERE'!$I$2:$I$1000000,">0")

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1
2				12					1	Outbound
PASTE HERE

 

[Jhpirate]

The biggest problem is the numbers that are involved in the calculation are stored as text or an otherwise unreadable format that excel is very picky about. The original formula that I had was
COUNTIFS('PASTE HERE'!$D$2:$D$1000000,A11,'PASTE HERE'!$J$2:$J$1000000,"Outbound")
I tied to change it so that the formula would count only values greater than 0 but it only spits out 0 in return when I know there should be something close to 44

 

[Jonmo1]

You need to convert those to real numbers.

Try copying a blank/empty cell
Highlight column I
Right Click - Paste Special - Values - Add
OK

 

[Jhpirate]

If at all possible I would like to avoid converting the data because this sheet is going to be used with different data every week so it needs to allow for the data in the format that it is already in. I'm pretty sure the problem with the calculation is that the numbers are stored in a time format like 00:00:00 and it's not actually formatted like time. Is there any way around this within the formula? I know this is confusing if clarification needed let me know.

 

[Jhpirate]

Actually, I think I just solved my problem. My original formula was correct except I needed to make it look like this
=COUNTIFS('PASTE HERE'!$D$2:$D$1000000,A11,'PASTE HERE'!$J$2:$J$1000000,"Outbound",'PASTE HERE'!$I$2:$I$1000000,">00:00:000")
I don't get why it has to be 00:00:000, but this makes it work exactly as needed