Sumproduct

[EXCELNEWCOMER]

Hi I've tried to work out my problem and have read the SumProduct notes but just cant seem to get it right.

what I need to do:-

If column C (rows 15-2500) = "Cheque Deposit" and column B = "Jan" then add the amount in column G as a running total.

Many thanks

David

[NBVC]

Try

=Sumproduct(--($C$15:$C$2500="Cheque Deposit"),--($B$15:$B$2500="Jan"),$G$15:$G$2500)

This only works if "Jan" is text string written that way and not a date formatted as MMM

If so, then

=Sumproduct(--($C$15:$C$2500="Cheque Deposit"),--(TEXT($B$15:$B$2500,"MMM")="Jan"),$G$15:$G$2500)

[martindwilson]

deleted -same as nbvc

[EXCELNEWCOMER]

Hi I have put it in as follows but it is not picking up a number

=SUMPRODUCT(--($C$15:$C$2500="*Cheque Deposit*"),--($B$15:$B$2500="*Jan*"),$G$15:$G$2500)


Paying
Jan £0.00 should be £1111
Feb £0.00
Mar £0.00
Apr £0.00
May £0.00
June £0.00
July £0.00
Aug £0.00
Sept £0.00
Oct £0.00
Nov £0.00
Dec £0.00
Date Issued Transaction Type Payable to or Recd From Chq # CC Date Amount Paid Amount Recd Recon'd
1st January Cheque Deposit Allianz - Cornhill 11 22nd September £1,111.00 £5,890.42 Yes
22nd September Commission from Insurer(Name Pls) Property Risk Mangement 12

[NBVC]

Why do you have "wildcards" around "Jan"?

is it an actual date in that range?

Is so, then use:

=Sumproduct(--($C$15:$C$2500="Cheque Deposit"),--(TEXT($B$15:$B$2500,"MMM")="Jan"),$G$15:$G$2500)

and "Jan" in the formula above can be replaced by a cell reference in your summary table, so that you can copy the formula down without editing it..

[EXCELNEWCOMER]

Hi the date is just a text string selected from a drop down list. I dont really understand what you mean. Basically need it to look at c if the word Cheque Deposit appears in it and colums b has Jan in it add up column g.

Its me being thick not your explanation but i dont have the knowledge to work it out.

[NBVC]

If they are text strings containing just the word "Jan" then the first one should have worked...

If more than just "Jan" is contained in the cell, then What about?


=Sumproduct(--($C$15:$C$2500="Cheque Deposit"),--ISNUMBER(SEARCH("Jan",$B$15:$B$2500),$G$15:$G$2500)

Maybe also look to see if your list has extra spaces before/after the strings... if so, fix them.

If still a problem, post a sample workbook.

[EXCELNEWCOMER]

Hi I attach a copy of the worksheet as an attachment.

David

Thank you for your time.

[NBVC]

Try:

=SUMPRODUCT(--($C$15:$C$2500="Cheque Deposit"),--ISNUMBER(SEARCH(A2,$B$15:$B$2500)),$G$15:$G$2500)

copied down...

[EXCELNEWCOMER]

Sorry to be ignorant but what does "copied down" mean

[EXCELNEWCOMER]

It works. If you have time would you be able to explain how each bit works and what it does?

=SUMPRODUCT(--($C$15:$C$2500="Cheque Deposit"),--ISNUMBER(SEARCH(A2,$B$15:$B$2500)),$G$15:$G$2500)

Many thanks

David

[NBVC]

Sumpoduct() is well explained here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Take a look..