Nb.si.ens multiple columns
Solved
FREDINE66
Posted messages
3
Status
Membre
-
FREDINE66 Posted messages 3 Status Membre -
FREDINE66 Posted messages 3 Status Membre -
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
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 réponses
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
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
