I need multiple criteria met in a sumproduct formula. Please help.

I've been banging my head against this for days now.

I have 2 columns of data being populated by vlookups

Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example...

If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.

I've tried using many variations of a sumproduct formula and none of them work.

This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.

=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))

Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..

=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))

but this causes an error in the formula somehow that I can't figure out. I even tried this

=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))

and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.

I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work. If anyone could help me I'd really appreciate it.

Thanks.

How about this:

=SUMPRODUCT(--ISNUMBER($H$2:$H$110),--ISTEXT($I$2:$I$110))

Or....

=SUMPRODUCT(--ISNUMBER($H$2:$H$110),--($H$2:$H$110>0),--ISTEXT($I$2:$I$110))

Your second formula is halfway there. It's counting all the instances of a number greater than 0 in column H but it's not making the comparison to column I.

Something else that may be causing a problem is the blank space in column I is manufactured. What I mean is I have a vlookup in those cells that brings in data from another spreadsheet and if it find no data it inserts a blank. the vlookup runs like this.

=IF(ISERROR(VLOOKUP(A2,'FILE PATH\[WORKBOOK.XLS]MONDAY'!$A$2:$D$3000,4,0)) ," ", (VLOOKUP(A2,'FILE PATH\[WORKBOOK.XLS]MONDAY'!$A$2:$D$3000,4,0)))

The bolded red area is what gives me a blank in the cell as opposed to just leaving it with an error (#VALUE)

I'll try massaging your formula to see if I can find a solution but the obvious choice (to me) didn't work.

=SUMPRODUCT(--ISNUMBER($H$2:$H$110),--($H$2:$H$110>0),--ISTEXT($I$2:$I$110<>"*"))

And thank you for helping me out.

The second formula works for me as is.

Please post a sample workbook with data showing it not working properly.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Well, your use of " " in column I is throwing off the ISTEXT() test. Even changing to a null string "" wouldn't help. Let's just test the length of the text string that IS there....

=SUMPRODUCT(--ISNUMBER($H$2:$H$110),--($H$2:$H$110>0),--(LEN($I$2:$I$110)>1))

AwF2, I'm glad you got a solution to your problem.

In the future, please create threads with appropriate titles. Just "SUMPRODUCT" doesn't really describe your problem, it relates to the solution. Thanks!