SUMPRODUCT problem

  • Thread starter Thread starter Brian Clarke
  • Start date Start date
B

Brian Clarke

I have a list where columns B - H are totaled in column I, and columns J - P
totaled in column Q. In column R, I want to show the difference between I
and Q, but only if there is at least one entry in cols B - H, AND at least
one entry in cols J - P.

I worked out this, which ought to work but doesn't.

=SUMPRODUCT((SUM(J84:P84="")<7)*(SUM(B84:H84="")<7)*(I84-Q84))

Where am I going wrong? This formula returns the difference between I and Q,
regardless of whether all the entries in B - H and J - P are blank.
 
Stefan,

Thanks for your reply. I should have explained that if there is anything at
all (even a zero) in columns B - H or J - P, I want the difference between I
and Q to appear to in column R.

Brian
 
=if(sumproduct((B84:H84="")*1, (J84:P84="")*1)=8;"";I84-Q84)

should work. hope so!

Stefan
 
=IF(AND(COUNTA(B2:H2)>0,COUNTA(J2:P2)>0),I2-Q2,"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Maybe checking those ranges using =count() or =counta().

To check for numeric entries:
=if(and(count(b84:h84)>0,count(j84:p84)>0),i84-q84,"no entries")

to check for any entries:
=if(and(counta(b84:h84)>0,counta(j84:p84)>0),i84-q84,"no entries")
 
Many thanks to all. Those answers work.


Bob Phillips said:
=IF(AND(COUNTA(B2:H2)>0,COUNTA(J2:P2)>0),I2-Q2,"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
 
=if(sumproduct((B84:H84="")*1, (J84:P84="")*1)=8, "", I84-Q84)
no semicolons, use commas - sorry!
we use semicolons over here in Germany ;-)
Stefan
 
Back
Top