SUMPRODUCT - 2 Criteria

  • Thread starter Thread starter Sam via OfficeKB.com
  • Start date Start date
Hi Domenic,

Apologies to you and Teethless mama. I forgot that I'd conditionally
formatted Column "I" to hide errors.

Answered questions below.

Cheers,
Sam
I've read through this thread and find myself somewhat confused.
Click to expand...
You mentioned the second criteria was giving you the problem. My
understanding is that the second criteria has to do with Column I.
Yes

However, when I asked for the formula, it looks like the formula you
posted has to do with Column H.
Click to expand...
Can you please clarify?
Click to expand...

Yes, apologies.

This is the Formula for Column "I"
=INDEX(Hire,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($A5),MONTH($B$4),1),ROW(Hire)-
MIN(ROW(Hire))+1),1))
By the way, in your first post you mentioned that the formula returned
#NUM!. Does your range contain #NUM! values? You can test it by trying
the following...
Click to expand...
=COUNTIF(Range,#NUM!)
Click to expand...

Yes, my sincere apologies. I forgot that I'd conditionally formatted Column
"I" to hide errors.
 
In that case, try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF($H$5:$H$42="102/2",IF(ISNUMBER($I$5:$I$42),IF($I$5:$I$42>0,1))))

Hope this helps!
 
Hi Domenic,

Thank you very much for your time and assistance. Especially, sorting out my
muddle. Formula works Great! Thanks again.

Cheers,
Sam
 
Hi Teethless mama,

Apologies, I forgot that I'd conditionally formatted Column "I" to hide
errors. The #NUM error was hidden away.

Cheers,
Sam
 
Back
Top