Thread: Help with Multiple Conditions

*Updated*

The below was solved.

Now that I have solved the checks portion, I am now looking to solve another complex piece. I am trying to do a COUNT formula for the checks.

Here is what I am trying to accomplish, I would like to COUNT all words that say CHECKS in column K that also fall within the month (i.e. Jan) in column A.

If anyone else can help, it would be very appreciated.


The solving piece to the puzzle below was

=SUMPRODUCT(IF(ISERROR(SEARCH("Nov",'Sheet1 Statement'!A9:A1030)),0,1)*IF(ISERROR(SEARCH("CHECK",'Sheet1 Statement'!K9:K1030)),0,1)*('Sheet1 Statement'!D9:D1030))

Thanks again!
____________________________________
*Original Post*

Good day,

I am fairly good with basic Excel code but have come across a little issue when trying to come up with a formula which will work.

I am trying to create an analysis of a bank statement. I have succeeded in getting much of the information I need, with a little work around, but I would like to reach out for some help.


There are a total of 5 “Sheets” which I will be manipulating in order to achieve this.

“Sheet1” is a copy of the bank statement
“Sheet2” will house all the finished data
“Sheet3” will be used for month 1 (if needed)
“Sheet4” will be used for month 2 (if needed)
“Sheet5” will be used for month 3 (if needed)

I want Excel to sort the data by month to include: credits, debits, number of checks, and total checks cashed.

So far, I have been able to use the formula in ‘Sheet2’ final data
=SUMIF(‘Sheet1’!A9:A1000, “*NOV*”, ‘Sheet1’!C9:C1000) to calculate each months credits and debits

Throughout this process I will be using rows 9-1000. The columns needed are as follows
A=Date
C=Credits
D=Debits
K=Description

Using a formula, I would like to do one of two things:

First, I would like a formula that reads is the SUM (Amount in column D) that (contains the partial word “*JAN*” from column A) AND (contains the partial word “*CHECK*” from column K).

I want Excel to look for all the data that says January AND Check and populate a SUM of the data in column C which is correlated with both columns A (Date) and K (Description).

Or Second, I have seen that it is hard to do 2 conditional statements so I would be willing to try a MACRO that would do the following:

I want a MACRO that says copy all rows that contain the partial text “*JAN*” and copy them to ‘Sheet3’
THEN/AND
Copy all rows that contain the partial text “*FEB*” and copy them to ‘Sheet4’
THEN/AND
Copy all rows that contain the partial text “*MAR*” and copy them to ‘Sheet5’

It does not matter where, on the ‘Sheet#’, they end up, but preferable start at A1

Since I am unfamiliar with MACROs, I would like a good description on how and where to past the code in the editor.

I appreciate you time and consideration in helping me out.

Sample Data

A: January,12, 2012
B: NA
C: $600.00
D: $250.00
E-J: NA
K: CHECK

Hi and welcome to the forum.

First, I would like a formula that reads is the SUM (Amount in column D) that (contains the partial word “*JAN*” from column A) AND (contains the partial word “*CHECK*” from column K).

I want Excel to look for all the data that says January AND Check and populate a SUM of the data in column C which is correlated with both columns A (Date) and K (Description).

Try this.

=SUMPRODUCT((A1:A1000="Jan")*(K1:K1000="check")*(D1:D1000))

I am not familiar with VBA, so i can not help you in your second question that you need(as you say) VBA solution.

Hope to helps you.

Unfortunatley, the formula returns a value of $0.00 which is not correct.
=SUMPRODUCT((A1:A1000="Jan")*(K1:K1000="check")*(D1:D1000))

Attached is a copy of the spreadsheet which is a trial of the worksheet.

I am hoping to get the output to read $702 for January

Thanks again for your consideration and time.

Hi

There is no possibility this formula to works for you....

=SUMPRODUCT(('Sheet1 Statement'!A9:A1030="*NOV*")*('Sheet1 Statement'!K9:K1030="*CHECK*")*('Sheet1 Statement'!D9:D1030))

1) No need of * in the formula.
2) Trere is nowhere Date Format(In columns with dates). So how excel can "understant", that when you type "NOV", you mean November?? ...And for what year??

I suggest you to type all the dates, as dates(any format you like-but date format) and fix all the Sheet with the other formulas and then we can do this function(Sumproduct), to work.

Hoping that with my poor English, give you to understand what i mean...

Note that SUMPRODUCT does not acept wildcard.
Normally we use (LEFT(RANGE,n)="text") to scan RANGE.
But in this case, the second criteria "CHECK" can be anywhere in string.
Therefore, we use SEARCH("text",RANGE) to scan.

=SUMPRODUCT(IF(ISERROR(SEARCH("Nov",'Sheet1 Statement'!A9:A1030)),0,1)*IF(ISERROR(SEARCH("CHECK",'Sheet1 Statement'!K9:K1030)),0,1)*('Sheet1 Statement'!D9:D1030))

Confirmed by Ctrl-Shift-Enter rather than using Enter only.

Wildcard is OK for COUNT, COUNTIF(S),SUM,SUMIF, SUMIF(S).
=COUNTIFS('Sheet1 Statement'!K9:K1030,"*CHECK*",'Sheet1 Statement'!A9:A1030,"*Jan*")

Since COUNTIFS is not valid in Excel 2003, how would you write it for Excel 2003

Using formula
=COUNTIF('Sheet1 Statement'!K9:K1030,"*CHECK*")-COUNTIF('Sheet1 Statement'!A9:A1030, "*JAN")
does not elimnate those in the months Nov or DEC and results in total "CHECK" which is 12.

Thanks again!