Multiple Row and Column Criteria

Punweet Singh

New Member
Joined
Sep 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Good luck, I hope you get it working the way that you want.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,156
Latest member
Ciupanezul21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top