View original (French)

Nb.si.ens multiple columns

Solved
FREDINE66 Posted messages 3 Status Member -  
FREDINE66 Posted messages 3 Status Member -
Hello

I am trying to calculate the number of "Surcharge Rature" in columns G, H, I, J of sheet 1 named "Tracking Table" if in column B of sheet 1 = F5

=COUNTIFS('Tracking Table'!B:B, F5, 'Tracking Table'!G:G, "Surcharge Rature", 'Tracking Table'!H:H, "Surcharge Rature", 'Tracking Table'!I:I, "Surcharge Rature", 'Tracking Table'!J:J, "Surcharge Rature")
0

=COUNTIFS('Tracking Table'!B:B, F5, 'Tracking Table'!G:H, "Surcharge Rature")
Error

for your information
=COUNTIFS('Tracking Table'!B:B, F5, 'Tracking Table'!G:G, "Surcharge Rature")
works very well, but I am missing columns H, I, J

Thank you for your help.

Sandrine

4 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    without the file, a bit randomly then:

    there is no reason you should be missing any columns if the formula works well for the first three
    unless the sought text (either Surcharge Rature) might not be written correctly in those columns
    check it, or try searching for just one of the two words, for example with:

    =COUNTIFS('Tracking table'!B:B; F5; 'Tracking table'!G:G; "*Rature*"; 'Tracking table'!H:H; "*Rature*"; 'Tracking table'!I:I; "*Rature*"; 'Tracking table'!J:J; "*Rature*")

    with the word Rature surrounded by asterisks and quotation marks
    thus all texts containing Rature will be taken into account
    note also that the complete formula you display in your message lacks quotation marks on the last text

    However, this formula will only calculate the number of rows that contain the text in all the specified columns.
    To get the number of surcharge rature without eliminating incomplete rows, use (limiting the fields to the useful row height as it is a bit heavier):

    =SUMPRODUCT(('Tracking table'!B2:B1000=B5)*('Tracking table'!G2:J1000="Surcharge Rature")

    and if it still doesn’t work, try uploading an example of the problem here:
    (with a few explanations on what you expect)
    http:mon-partage.fr
    and come back to paste the created link on the site

    looking forward to hearing from you

    best regards
    1