SUMPRODUCT Question!

[tumblingbay]

Hi,

I am having problems with a SUMPRODUCT formula and have searched high and low for a solution with very little success! Hopefuilly someone here can help!

I have three columns and am trying to calculate the total of the third where the first two columns meet certain criteria. The following entry does exactly what I need:

=SUMPRODUCT(--(N5:N13="ACXA"),--(O5:O13=123),P5:P13)

...however; I need the formula to return values where the information in the second column starts with a '1', i.e. 123 or 124 or 155 and so on...

I tried to use wildcards but they dont seem to work against numbers however I did manage to get the following fomula working:

=SUMPRODUCT(--(N5:N13="ACXA"), ((LEFT(O5:O13)="1")*1))

...which returns the number of occurences of the first two columns but I cannot figure out a way of bringing the third 'totals' column in!

Please help!

TB

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=SUMPRODUCT(--(N5:N13="ACXA"),--(LEFT(O5:O13,1)="1"),P5:P13)

 

[RoryA]

=sumproduct(--(n5:n13="acxa"),--(left(o513,1)="1"),p513)

 

[PaddyD]

Hi, welcome to the board.

Keeping the syntax consistent:

=SUMPRODUCT(--(N5:N13="ACXA"), --(LEFT(O5:O13)="1"),P5:P13)

 

[tumblingbay]

wow - three perfect responses in 10 minutes!! I've been pulling my hair out for considerably longer!
Thank you so much!