Forumula Help

[ROYW1000]

Hi

Can anyone help with a formula to do the following.

1) If nothing is entered in split (B11) it show the total spend for customer 529908 but if you enter in B11 CCTV it would only calculate customer 529908 and it would should £300 but if you entered Access it would only show 50.

Thanks
Roy

[JohnTopley]

And how do we know which customer???

Look at SUMIF/SUMIFS functions.

[ROYW1000]

Hi

The customer is just using the numbers in column A and I looked and SUM and SUMIF but had trouble nesting them and getting it to wrok.

Thanks
Roy

[shukla.ankur281190]

Try

Please Login or Register  to view this content.

[JohnTopley]

Trt

=SUMIFS($V$2:$V$6,$A$2:$A$6,"529908",$U$2:$U$6,IF($B$10<>"",$B$10,"*"))

but I would put required customer number in a cell e.g. A10

=SUMIFS($V$2:$V$6,$A$2:$A$6,$A$10,$U$2:$U$6,IF($B$10<>"",$B$10,"*"))

[ROYW1000]

Hi

Thanks for the help this is working perfectly.

=IF(B11="",SUMIFS(V:V,A:A,529908),SUMIFS(V:V,A:A,529908,U:U,B11))

Roy

[JohnTopley]

Good!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.