# SUMPRODUCT - Multiple Criterias with partial keyword

1. ## SUMPRODUCT - Multiple Criterias with partial keyword

Hi Experts,

Good day

I have column A with dates, column B with Descriptions and column C with Amounts

DATE DESCRIPTION AMOUNT
27-Apr-22 ABCD 200
27-Apr-22 ABCD -1000
27-Apr-22 CT ABCD -3000
26-Apr-22 ABCD 1000
26-Apr-22 ABCD 600
26-Apr-22 CT ABCD -480
26-Apr-22 ABCD 25
26-Apr-22 CT ABCD -2020
26-Apr-22 ABCD 2700
25-Apr-22 ABCD 1986
25-Apr-22 ABCD 40

I'm looking for a formula to SUM the amount from specific date, description (partial keyword "CT")

Example :

I've input 27-Apr-22 in E1, in F1 i need to answer for 27-Apr-22 total of negative amount only for CT ABCD

I manage to find the figure for negative amount but still need to sort out the description part

Attached herewith my sample of workbook

2. ## Re: SUMPRODUCT - Multiple Criterias with partial keyword

Try this:

=SUMPRODUCT(((A2:A12=E1)*(LEFT(B2:B12,2)="CT")*(C2:C12<0))*(C2:C12))*-1

or:

=ABS(SUMPRODUCT(((A2:A12=E1)*(LEFT(B2:B12,2)="CT")*(C2:C12<0))*(C2:C12)))

3. ## Re: SUMPRODUCT - Multiple Criterias with partial keyword

Hi AliGW,

Million Thanks~

Much Appreciated

4. ## Re: SUMPRODUCT - Multiple Criterias with partial keyword

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1