SUMPRODUCT problem

[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(J8484="")<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 Holz]

Hi Brian,

why don't you try

=if(sum(i,q)>0,i-q,"")

Stefan

 

[Brian Clarke]

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

 

[Stefan Holz]

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

should work. hope so!

Stefan

 

[Bob Phillips]

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Maybe checking those ranges using =count() or =counta().

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

to check for any entries:
=if(and(counta(b84:h84)>0,counta(j8484)>0),i84-q84,"no entries")

 

[Brian Clarke]

Many thanks to all. Those answers work.

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[Stefan Holz]

=if(sumproduct((B84:H84="")*1, (J8484="")*1)=8, "", I84-Q84)
no semicolons, use commas - sorry!
we use semicolons over here in Germany ;-)
Stefan