Count non-consecutive blank cells in a row if condition is met

[abschy]

Hi all,

I have a table as such below, I'm having some issues creating a formula that can do the following:

1. If Region = A
2. Count the number of blanks in that row for the columns
- Region
- Country
- Name
- Gender
- Weight

So for Region A, i should get back a value of 1 as the City column is not included in the count.

Is there a way to do this such that i dont have to use sumproduct? My actual data set has about 20 columns and it doesnt seem efficient to use sumproduct this way..

Thank you!

Region	Country	City	Name	Gender	Weight
A	ABC	X	Jane	F
B	DEF	Y	John		80
A	ABC		Mary	F	50

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[abschy]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Peter! Thanks for the advice, i've just added the information.

However, the file that i am working on will be used by multiple people across the world as it will be uploaded onto a sharepoint. If it works on my file, would that also work on other versions..?

 

[Peter_SSs]

i've just added the information.

Thanks for doing that.

If it works on my file, would that also work on other versions..?

A solution suggested that works on your 365 version will quite probably not work on versions lower than 365, though it might depend on exactly which version(s) your other users have.
Post #1 sounded like you have a SUMPRODUCT formula that does the job and that should work on all versions so you may be safest to stick to that.