Sumproduct or Countif?

R

Renee'

Hi,
Can anyone help with the following:

Column A Column B Column C
SPV 1 1
TMI 1
RCG 1
SPV 1

I need a formula that will return/count only "1" if EITHER of the 2 adjacent
columns have entries.
My sumproduct formula is multiplying by 0 when one of the is empty.
Any help is greatly appreciated
 
A

Alex Delamain

I couldn't think how to do an either / or without using another colum
so I ended up with "not two blanks". And what do you now - its got
coutnif and a sumproduct!


=COUNTIF(A1:A100,">""")-SUMPRODUCT((B1:B100="")*(C1:C100="")*(A1:A100>0)
 
B

Bernie Deitrick

Renee,

If you want 3 as the answer (the case of either but not both):
=SUMPRODUCT((C1:C4=1)*(B1:B4=""))+SUMPRODUCT((C1:C4="")*(B1:B4=1))

If you wanted 4 as the answer (the case of either is 1) array enter - using
Ctrl-Shift-Enter:
=SUM(IF(B1:B4=1,1,IF(C1:C4=1,1,0)))

HTH,
Bernie
MS Excel MVP
 
R

Renee

I don't think I explained my question very clearly, I needed a return count
of "1" whether both columns (B &C), or either column was checked. Your
formulas helped me come up with the following, which appears to work... I'm
not sure why. I'd welcome an explanation
=SUMPRODUCT((A12:A24="tmi")*(G12:G24<""))-SUMPRODUCT((G12:G24="<0")*(H12:H24<""))+SUMPRODUCT((G12:G24="")*(H12:H24<""))

For "SPV" in column A, I get a return count of 2

THANKS FOR ALL THE HELP!
 
B

Bernie Deitrick

Renee,

I think a better solution would be more specific: the three cases you want
to count are 1 in G, blank in H: blank in G, 1 in H, 1 in G and 1 in H, all
with the same value in A. So use a formula like this, all on one line:

=SUMPRODUCT((A12:A24="tmi")*(G12:G24=1)*(H12:H24=""))
+SUMPRODUCT((A12:A24="tmi")*(G12:G24="")*(H12:H24=1))
+SUMPRODUCT((A12:A24="tmi")*(G12:G24=1)*(H12:H24=1))

You could also use another cell, one that contains "tmi", for example:
=SUMPRODUCT((A12:A24=A1)*(G12:G24=1)*(H12:H24=""))
+SUMPRODUCT((A12:A24=A1)*(G12:G24="")*(H12:H24=1))
+SUMPRODUCT((A12:A24=A1)*(G12:G24=1)*(H12:H24=1))

You could also use the shorter array formula (Entered with Ctrl-Shift-Enter)

=SUM((A12:A24="tmi")*(IF(((G12:G24=1)+(H12:H24=""))>=1,1,0)))

HTH,
Bernie
MS Excel MVP
 
R

Renee

Once again, I think I lead you guys astray, but I think/hope this time it's
ok...the numerical entries in columns B & C will be sometims be larger than
one. I altered the formula as follows and it's working so far... but so was
the one I mentioned below..for a moment. Will this minor change do the trick?

=SUMPRODUCT((A14:A26=A3)*(G14:G26>=1)*(H14:H26=""))+SUMPRODUCT((A14:A26=A3)*(G14:G26="")*(H14:H26>=1))+SUMPRODUCT((A14:A26=A3)*(G14:G26>=1)*(H14:H26>=1
 
B

Bernie Deitrick

Renee,

Yes, your changes should work for the long term. But I hope you mean the
values in columns G and H, not B and C....

Also, your first 'formula that you mentioned below' didn't examine column A
form the values there, and would have failed when looking at data sets with
multiple deiffereing terms in column A (unless that was what you actually
wanted ;-))

HTH,
Bernie
MS Excel MVP
 
R

Renee

Yes, "G" & "H". Thanks for all your help

Bernie Deitrick said:
Renee,

Yes, your changes should work for the long term. But I hope you mean the
values in columns G and H, not B and C....

Also, your first 'formula that you mentioned below' didn't examine column A
form the values there, and would have failed when looking at data sets with
multiple deiffereing terms in column A (unless that was what you actually
wanted ;-))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top