• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a multiple array countif (at least trying to)

R

Richardd

New Member
Hi,


Not really sure how to describe the issue so have not been able to search for anything similar if it exists.


I've currently got a data sheet that lists multiple potential reasons for a product defect.


This range will be dynamic and could cover 4-8 columns dependent on the type of display. I will also need to amend the range to only cover certain columns as the data is specific to a certain date range. The dynamic side I can handle however)


An example of this matrix is here (not sure how to format but copying back into Excel should be fine):


Reason Set 1 Reason Set 2 Product 1 Name Product 2 Name Product 3 Name

Zero Stock Product A

Broken Product Product C Product B

Zero Stock Product A Product B Product C

Dirty Product Product D Product A Product B

Display missing Product C Product B


I would like to be able to fill the following table (again, apologies for lack of format):


Product A Product B Product C Product D

Dirty Product

Display missing

Broken Product

Zero Stock


For example: Product A should show a 2 against zero stock and 1 against dirty product. Product B should have 1 against all reasons.


I've tried multiple array formulas to absolutely no avail. When the reasons were in one column I can get it done with an array SUM formula, but this is not practical due to the nature of the sheet.


Ideally, i'd like to avoid VBA as well, but if forced to it can be used. Thanks in advance!


I've uploaded file here.

https://www.dropbox.com/s/23c0pz87xr1sjnm/ChandooQuestion.xlsx
 
HI Richardd,


Welcome to the fourms!


Can we have a case where both reason 1 and 2 are present for a product?? or it will always be single reason?


Regards,
 
Hi Faseeh, thank you.


Nope - it will be either set 1 or set 2. The sets are answered depended on previous question responses.
 
Ok,


Let me upload my workout!


https://dl.dropboxusercontent.com/u/60644346/ChandooQuestion.xlsx


See above.


Regards,
 
Welcome to Chandoo.org forums.


You have stated your problem quite nicely and plus uploaded a sample workbook. Well done.


In cell H3 of your uploaded workbook Array Enter (CTRL + SHIFT + ENTER) following formula:

=SUM((ISNUMBER(FIND(H$2,$C$1:$C$6&$D$1:$D$6&$E$1:$E$6,1))*(ISNUMBER(FIND($G3,$A$1:$A$6&$B$1:$B$6,1)))))


If entered correctly Excel will show curly braces {} in the formula bar.


Copy the formula down and across, it should get you the results you are looking for.


@Faseeh: Nice formula. You do not need to multiply it by 1.

i.e.

=SUMPRODUCT(($C$2:$C$6=$H3)*($D$2:$F$6=I$2))

will work fine just as

=SUMPRODUCT(($C$2:$C$6=$H3)*($D$2:$F$6=I$2)*1)

does
 
Thank you both very much. I really wasn't expecting such a quick response, I still have to do a little more sheet-work before I can implement it!


Faseeh - Just noticed the helper column, unfortunately my sheet can't really afford to have that as the cells the data is held in is in a much deeper table of data. But thank you.


Shrivallabha - Your solution looks awesome! Once I get my head around it! Thank you!


Hopefully I don't come back with any more questions.
 
@ Richardd,


Replace the formula in I3 with this one and there will be no need for helper column, drag to right and down:


Code: 
=SUMPRODUCT((TRIM($A$2:$A$6&$B$2:$B$6)=$H3)*($D$2:$F$6=I$2)*1)


regards,

Faseeh
 
You must log in or register to reply here.
Back
Top