0

I would like to count the number of times my products are sold. I tried COUNTIF, but it does not take into account the quantity associated obviously. In the columns E G I I have the number of products and in the columns F H J I have the name of the product In the screen 1 we see : 5 DRA 7 FILE 2 MUS 1 Gra 1 He

But COUNTIF will find : 5 DRA 4 FILE instead of 7 etc.

Is there a function that could take into account the quantity of columns E G and J at the same time?

Link : https://docs.google.com/spreadsheets/d/1_hXWlkJxzP-7QbUW6G4Ymk4Zzg2TxfzDWNEp1MGYl2Y/edit?usp=sharing

Thanks a lot for your help :)

Screen 1

5
  • There are ways to do this, yes. However, I don't see the reason for blurring of fields, which is confusing matters. And it is difficult to help you from an image; this implies that you expect the volunteers here to type in some kind of data into their own spreadsheet (data that can't even be seen in your blurred image) before they could even begin to help. Consider sharing a link to an actual sample spreadsheet with enough data in it to clearly illustrate the full problem. Use fake product names if you must.
    – Erik Tyler
    Dec 6 '21 at 14:04
  • In addition, you clearly have more than three sets of Quantity/Product columns going right. And you have some unknown amount of data above and below what is shown your posted image, based on the row numbers shown. So the volunteer contributors here don't even know the actual range to which such a formula would apply. Again, consider sharing a link to an actual sample sheet that realistically represents the full scope and range of the problem.
    – Erik Tyler
    Dec 6 '21 at 14:06
  • Hello you're right it's more easy with a link : docs.google.com/spreadsheets/d/… You can find my data here I just delete name and address of customers for confidentiality of course. Thanks a lot for your help :)
    – Fabien
    Dec 6 '21 at 19:36
  • Adding the link definitely helps. However, it's unclear where you want the formula and its resulting counts to go. Please clarify.
    – Erik Tyler
    Dec 6 '21 at 20:02
  • On the sheet "LISTE PDT" in the column H, I want to fill the quantity of product sell. On the column A from the same sheet, there is the name of the product. And I have to find all quantity in the sheet "COMMANDES". In the sheet "COMMANDES" all orders are in column E,F,G,H,I,J,K,L Do you need more information ? Thanks a lot for your time !
    – Fabien
    Dec 6 '21 at 21:30
0

Delete everything from 'LISTE PDT'!H:H (including the header). Then place the following formula in 'LISTE PDT'!H1:

=ArrayFormula({"Vendu"; IF(A2:A="";;IFERROR(VLOOKUP(A2:A;QUERY({FLATTEN(FILTER(PROPER(COMMANDES!E2:L);REGEXMATCH(COMMANDES!E1:L1;"Pdt")))\FLATTEN(FILTER(COMMANDES!E2:L;REGEXMATCH(COMMANDES!E1:L1;"Qt")))};"Select Col1, SUM(Col2) WHERE Col2 Is Not Null GROUP BY Col1");2;FALSE)))})

This one formula will produce the header and all column results.

I should begin by noting that you have variations in the way that product names are being rendered (e.g., "Colossal V1 Without Smoke" and "Colossal V1 without Smoke" noting that the word "without" is capitalized differently), and that affects what REGEX-type commands consider to be a match. So the formula here requires one PROPER( ) wrap to normalize these differences.

How It Works

You will see FILTER(FLATTEN(____,REGEXMATCH())) twice. In each case, FILTER will filter in only the column results where REGEXMATCH on the column headers matches a specific value ("Pdt" the first time and "Qt" the second time). FLATTEN then forms one column of each respective array, ending with the products in a left-side column and the raw quantities in a right-side column (all in memory and unseen).

QUERY then eliminates any rows of the above array which had no counts, and then it returns all products and SUMs of counts per product.

VLOOKUP then looks up every non-null value in A2:A within this virtually-held QUERY and returns the Column-2 result (i.e., the SUM) for the corresponding product.

The outer IF wrap leaves the results null where A2:A is also blank/null.

2
  • Wow It's so smart ! It's a next level... Great thanks for your help, it works very well. And big thanks for explanations too !
    – Fabien
    Dec 7 '21 at 11:40
  • Glad it worked for you. If you would, please take a moment to mark my post as "Best Answer." This allows the contributor community to see that this issue has been fully resolved; and it will allow future site visitors to quickly find this answer.
    – Erik Tyler
    Dec 7 '21 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.