Thread: Summing & Arrays

Hi,

I have a database where column M and N have systolic and diastolic blood pressure values. Column G has the *** i.e. either M or F or it is blank if unknown.

I need to calculate:

a) sum of the number of people that have either M>139 OR >89

So far I have tried to use SUMPRODUCT and SUMIF. But nothing that works as of yet. I could do it using several different calculations but I'd prefer it if one formula could do it instead because I'll need it for the second point below.

b) calculate how many of the above hypertensives are male and how many female.

So far I have tried:

=SUM((Data!G2:G238="M")*((Data!M2:M238>139)+(Data!N2:N238>89)))
=SUMPRODUCT(IF(Data!$G$2:$G$237="M",--(Data!$M$2:$M$238>=140),--(Data!$N$2:$N$238>=90)))

Thanks in advance,

I'm unable to post the actual Excel file or screen shots due to confidentiality.

I have attached a file with some data so that you can see what I mean. Any help with this would be really welcome.

Update:

for a) If I use the code

=SUM((Data!$M$2:$M$238>139)*(Data!$N$2:$N$238>89))

Then I get the number of people who have high systolic (M) AND diastolic (N) BP. But I want the number of people who have high systolic (M) OR diastolic (N).

=SUM((Data!$M$2:$M$238>139)+(Data!$N$2:$N$238>89))

This code gives me the wrong answer but I'm not sure if it is correct or not?

I realise I could just use a different column in the Data spreadsheet to write something like

=IF(OR(M2>139,N2>89),1,0)

and then sum the whole thing up at the bottom, but this won't help me at all with the formula for b). This is why I am still stuck

Hi Kyalimers and welcome to the forum,

How about the attached that has two pivot tables. See if they help.

OK,

How about trying CountIf and CountIFS? See attached.

A visual count of hypertensives on your attachment is 25.
7 hypertensives are male, 16 are female and 2 are of unknown gender.

The count of 25 hypertensives is returned by...
=SUMPRODUCT((C2:C50>139)+(D2:D50>89))-SUMPRODUCT(--(C2:C50>139),--(D2:D50>89))
or...
=SUMPRODUCT((C2:C50>139)+(D2:D50>89)-(C2:C50>139)*(D2:D50>89))

[SUMPRODUCT((C2:C50>139)+(D2:D50>89)) counts those with high systol and high diastol twice so SUMPRODUCT(--(C2:C50>139),--(D2:D50>89)) has to be subtracted to overcome this double count]

The count of 7 male hypertensives is returned by...
=SUMPRODUCT(--(B2:B50="M"),(C2:C50>139)+(D2:D50>89)-(C2:C50>139)*(D2:D50>89))

The count of 16 female hypertensives is returned by...
=SUMPRODUCT(--(B2:B50="F"),(C2:C50>139)+(D2:D50>89)-(C2:C50>139)*(D2:D50>89))

Beau Nydal