Multiple Row and Column Criteria

[Punweet Singh]

Hi Everyone!

Your advice and support here would really go a long long way. I will try and explain my issue below as best as possible

Refer to Sample Sheet - Google Sheets - create and edit spreadsheets online, for free.

The data is Range A14:I36 will be copied daily into a this sheet. I have added some basic macros in order to retrieve the data in J14:AL36. Where i need your help is populating the data in D5:J10

For example - In cell D5 i need to be able to count all the cells from column K14:K36 that is equal to "6" or ">0" BUT it should also fit the criteria that the remaining values in row L14:AL14 is blank or "0" A similar situation follows for all the remaining cells in D5:J10

it is possible that a customer has ordered a Indian water 6 pack and also a grapefruit tonic water 12 pack in which case that will count in cell C9 (Other).

I hope this wasn't too confusing and ill be happy to try and answer any queries. thank you !

 

[Punweet Singh]

I am sorry, but I am utterly confused by what you want now.
Perhaps you could walk me through an actual example based on the data you have posted, telling me what value you want in the upper section, and show me exactly how you arrive at that value.

I apologize, i can understand how that's probably a bit confusing. Ill mention a few examples below based on the datasheet

1) The data in cell I12 is the standard format i receive all my orders in. You will notice "JFVP06-MFN mentioned 12 times in cell I12. VP denotes Variety Pack and 06 denotes Pack Size. The main information we require here is how many times it has been mentioned (12) and type of product (VP or Variety Pack)

What i need to do is take this data (I12:I28) and consolidate all my orders in the range D4:K7. The correct value for this example should be (1) added to cell J5. Similarly, any other orders that meet this exact criteria should be added to the same cell I5.

2) Cell I13 has the same product (VP06-MFN) but mentioned 6 times, Hence a value of 1 should be added to cell J4

3) Cell I14 is the came where the customer has ordered two different products (JFTW06-MFN) and (JFGP06-MFN) for a total of being mentioned 12 times. Since these two products are not part of any standard pack name. this should have a value of 1 added to cell 5K

Above all are the various combinations that can be possible in terms of 1) Multiple quantities of same product 2) Standard product and 3) Not standard product

Hope this information helps. Let me know if you have any queries. thanks!

 

[Joe4]

I guess I am just not understanding the issue. It seems like the COUNTIF formulas you have for C5:C9 should get you what you want.
Does that not get you what you want?
Or are you having issues creating the formulas in the lower section?

Can you point out an example of one that is NOT working correctly?
Let me know what value is currently being returned, and what that value SHOULD actually be?

 

[Punweet Singh]

So the countif formula will work for all except for the data in cell I16 But i think this could be an issue with creating the formulas in the lower section. Currently from the data in cell I16 it is adding a value of 6 in cell K16 and L16 respectfully and adding two values in the above sheet instead of 1. But since it is a non standard item and contains multiple products. I'm guessing we should add another column R and any data in column I that automatically contains more than one product name Example ( JFTW06-MFN,JFGP06-MFN) should be added to this new column R along with the pack size. Then the countif formula above should work for this.

 

[Joe4]

I am sorry, I am having a hard time working with this. The structure of the data makes it very difficult to work with.
I don't think I am able to be of further help.

Here is one last thing I will leave you with (not sure if it helps any, but maybe it will).
In my original formula, if you want to hone in on specific values, you can change it like this:

Original Formula:
=SUMPRODUCT(--($J$14:$J$36=K$14:K$36),--(K$14:K$36<>0))

To hone in on the "6"s:
=SUMPRODUCT(--($J$14:$J$36=K$14:K$36),--(K$14:K$36=6))

 

[Punweet Singh]

I am sorry, I am having a hard time working with this. The structure of the data makes it very difficult to work with.
I don't think I am able to be of further help.

Here is one last thing I will leave you with (not sure if it helps any, but maybe it will).
In my original formula, if you want to hone in on specific values, you can change it like this:

Original Formula:
=SUMPRODUCT(--($J$14:$J$36=K$14:K$36),--(K$14:K$36<>0))

To hone in on the "6"s:
=SUMPRODUCT(--($J$14:$J$36=K$14:K$36),--(K$14:K$36=6))

Ill give it a quick shot. thank you so much for trying. it means a lot! ill let you know how this goes.

 

[Joe4]

You are welcome.
Good luck, I hope you get it working the way that you want.