Counting one column if one of two other columns have a value

[xryanx]

A21:A30 are locations
C32:C2000 has names of locations
G32:G2000 has a dollar amount
H32:H2000 has a dollar amount

I need B21 to only count locations in C32:C2000 that correspond to A21 and have a value in either G21 or H21.

Example C32 is "Home". The formula sees that A21 is "Home", but also looks at G32 and H32 to see if either of them have a value. If so, then 1 is added to B21. If G32 and H32 are both blank, no count is added to B21.

Below is a graph. It show location "308" having 2, but should only show 1.

	A	B	C	D	E	F	G	H

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"><col style="width: 49px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]
[TD="align: center"]Home[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$105.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]
[TD="align: center"]308[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$40.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]
[TD="align: center"]328[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]24[/TD]
[TD="align: center"]355[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]25[/TD]
[TD="align: center"]430[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]26[/TD]
[TD="align: center"]512[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]27[/TD]
[TD="align: center"]NEL[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]28[/TD]
[TD="align: center"]LV[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]29[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]30[/TD]
[TD="align: center"]FQL[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]31[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]32[/TD]
[TD="align: center"]1/1[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]Home[/TD]
[TD="colspan: 2, align: center"]John Doe[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$40.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]33[/TD]
[TD="align: center"]1/1[/TD]
[TD="align: center"]124[/TD]
[TD="align: center"]Home[/TD]
[TD="colspan: 2, align: center"]Jane Doe[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]$65.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]34[/TD]
[TD="align: center"]1/2[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]308[/TD]
[TD="colspan: 2, align: center"]Doe John [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]$40.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]35[/TD]
[TD="align: center"]1/3[/TD]
[TD="align: center"]126[/TD]
[TD="align: center"]308[/TD]
[TD="colspan: 2, align: center"]Doe Jane[/TD]
[TD="align: center"]4[/TD]

</tbody>

 

[Marcílio_Lobão]

xryanx, Good afternoon.

Try to do:

B21 --> =SUMPRODUCT(($C$32:$C$35=A21) * (($G$32:$G$35>0)+($H$32:$H$35>0)))

C21 --> =SUMPRODUCT(($C$32:$C$35=A21) * (($G$32:$G$35)+($H$32:$H$35)))

Please, tell us if it worked as you want.
I hope it helps.

 

[lrobbo314]

The formula for B21...

=SUMPRODUCT(($C$32:$C$2000=A21)*(($F$32:$F$2000>0)+$G$32:$G$2000>0))

And C32...

=SUMPRODUCT(($C$32:$C$2000=A21)*(($F$32:$F$2000>0)+$G$32:$G$2000>0)*$F$32:$G$2000)

 

[xryanx]

Thank you both so much. I tried Marcilio's first and it worked.

 

[Marcílio_Lobão]

xryanx, Good morning.

Glad to have helped you.


Have a good Sunday!