SumProduct and SumIf

**J Morrow** · 05-24-2011, 12:51 AM

I recently received some great help through post "Multiple Conditional Count Functions" and now need to build upon that somewhat.

As per attached, there is data in the master tab, columns OTSD NOMEM and Other Open NTFY. Some cells are empty, some cells contains one or more bits of data (in this case they are sets of numbers but could also be words) with each bit of data seperated by a space.

I can count the total number of sets of numbers using the array formula:
{=SUM(IF(LEN(TRIM(Master!E2:E2500))=0,0,LEN(TRIM(Master!E2:E2500))-LEN(SUBSTITUTE(Master!E2:E2500," ",""))+1))}

I would like to add the functionability to only count the above when data in other columns match a value as was provided in the last post. The example was provided using a drop down solution (of which I did away with the drop down for now) as per the following:
=SUMPRODUCT(--(Master!$B$2:$B$2500=$A2),--(Master!$G$2:$G$2500=$B2),--(Master!$H$2:$H$2500=$C2),--(Master!$I$2:$I$2500=$D2))
Where A2 is the value of PM2 Required, B2 is the value of Base Location and on.

I have attached an example of the worksheet.

As before, I thank those power users for thier help!

J Morrow

**TMS** · 05-24-2011, 04:52 AM

I'm not sure what your question is. Are you trying to combine the two formulae?

The SUMPRODUCT formula works; the values are all zero because the criteria are not met.

REQUIRED | Base1 | Aux_DH | Bridle gives a count of 10.

Have I misunderstood?

Regards

**Marcol** · 05-24-2011, 06:05 AM

Are you trying to filter again by any number found in "OTSD NOMEM" and "Other Open NTFY" even when this number might be part of a text string?

e.g.
561 is found in "OTSD NOMEM" as a number and also contained in the string "394 421 367 546 480 561"

I have put some workings into your sheet to see if I'm reading your thoughts correctly.
This is not a solution. It's purely trying to clarify your problem.

**J Morrow** · 05-24-2011, 08:57 PM

The long formula was something that I used with another worksheet of which was the only solution that I could find to count the total number of "FMN" numbers in each cell (if more than one FMN in a given cell it would be separated by a space). The format for the FMN numbers, for example was FMN2011055. The long formula took into account the seperations in order for the count function to work correctly and ignored any cell which had no data.

True, in this case, I am counting actual set or sets of numbers of which are all numerical. And again, if more than "NOMEM" or "NTFY" in a given cell, the numbers are seperated by a blank space.

My current problem is when I attempt to count the total number of "NOMEM" with additional conditional filters. For example, when I add the filter for "Base Location", "Page" and "Section" qualifies, I cannot get my formulas to work. I can only seem to be able to use one of the above named filters but not two or three of them combined.