Wildcard replacement when using SUMPRODUCT

[Ziddo]

Hi all,

I´m frustrated that you kind people tries so many time to explain functions but still I do not quite get it to work.

=SUMPRODUCT(ISNUMBER(SEARCH("[P1";G4:G842))*(SUBTOTAL(109;F4:F842)))

I'm trying to find starting text "[P1" from column G4:G842 and from that result/filtered sum the values from F4:F842
Meaning that I should be able to filter on other columns and then only count the visable fields F4:F842

another try I had
=SUMPRODUCT(G4:G581="*[P1*";F4:F581)*SUBTOTAL(109;F4:F581)
=SUMPRODUCT(G4:G581="*[P1*")+SUBTOTAL(109;F4:F841)

Thank you in advance.

 

[Aladin Akyurek]

Hi,

It seams that only this first position is counting ... ?


https://drive.google.com/a/sederlin.com/file/d/0B-fy6KpnjT7CTHBCa0p5SHNWTzA/view?usp=sharing

=SUMPRODUCT(ISNUMBER(SEARCH({"[P101";"[P104"};G4:G843))*SUBTOTAL(109;OFFSET(F4:F843;ROW(F4:F843)-MIN(ROW(F4:F843));0;1;1)))


Sorry but I do not get a .jpg link to my file

If I Evaluate the formula I get some maybe interesting results:
https://drive.google.com/a/sederlin.com/file/d/0B-fy6KpnjT7CU0U0UDJUMlJXSDA/view?usp=sharing

Control+shift+enter, not just enter:

=SUM(IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(M1:M2),G4:G843)),ROW(M1:M2)^0),
  SUBTOTAL(9,OFFSET(F4,ROW(F4:F843)-ROW(F4),0,1))))

where M1:M2 houses the criterion strings of [P101 and [P104.

 

[pgc01]

Hi,

Thats strange ... it is very strange that I do not get any result at all "0"when I combine the P1 and P2.
Maybe it has someting to do with the "Regional Settings"?
I have to convert you "," to ";" to get the formula to work do you know if I have to convert "{" to someting else?

Hi Ziddo

If in your regional setting the comma is the decimal separator then the separators are not the same as with English settings. Ysually the comma is replaced with a semicolon for a function parameter separator and with a backslash for a horizontal array elements separator.

So, I replaced the separators for English settings for what I think are yours.

Please try, for the example I posted:

=SUMPRODUCT(ISNUMBER(SEARCH({"[P1"\"[P2"};G4:G842))*SUBTOTAL(109;OFFSET(F4:F842;ROW(F4:F842)-MIN(ROW(F4:F842));0;1;1)))

 

[Ziddo]

Hi pgc01,

It works so fine !!!!!!!!!!!!!!!!!!!!!!!
You mad my Friday worth waking up !


Thank you for all your kind explanations and effort!

THANK YOU!

ray:

Hi Ziddo

If in your regional setting the comma is the decimal separator then the separators are not the same as with English settings. Ysually the comma is replaced with a semicolon for a function parameter separator and with a backslash for a horizontal array elements separator.

So, I replaced the separators for English settings for what I think are yours.

Please try, for the example I posted:

=SUMPRODUCT(ISNUMBER(SEARCH({"[P1"\"[P2"};G4:G842))*SUBTOTAL(109;OFFSET(F4:F842;ROW(F4:F842)-MIN(ROW(F4:F842));0;1;1)))

 

[pgc01]

You're welcome. Thanks for the feedback.