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
21	Home	2	$105.00
22	308	2	$40.00
23	328
24	355
25	430
26	512
27	NEL
28	LV
29	AA
30	FQL
31
32	1/1	123	Home	John Doe	1	$40.00
33	1/1	124	Home	Jane Doe	2		$65.00
34	1/2	125	308	Doe John	3	$40.00
35	1/3	126	308	Doe Jane	4

<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>
</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!