Help figuring out a formula

Hi There,

I have a few questions:


REP 1.00 Jan 1,2009 Approved
SAK 1.00 Jan 2,2009 Approved
HW 1.00 Jan 4,2009 Denied
HW 1.00 Feb 1,2009 Denied
ITP 1.00 Feb 2,2009 Approved

I want to be able to compare "D" and "A" and add "B" for a result, is this possible?
So, if REP = approved, add all "approved value in B", result = 1.00
So, uf HW = denied, add all "denied value in B", result = 2.00

I want to sum up "B" by the January Month in "C", is it possible with the date broken out the way it is?

If someone could help me with these formula's I'd really appreciate it.


I can include my file if necesary

App Tracking.xls501.5 KB
Nick's picture

Help figuring out a formula

Hi Meagan

I am sure we can find a solution for you.

Please attach a file.
- this should only a good sample of the source data and an example of the summaries you want.

- Can you also explain the background behind the request.
Something like:
"I am trying to create a summary of expenses for people"

For Question 2, it is likely you can use the SUMIF formula if you add an additional column using the MONTH formula to back out the month


Help figuring out a formula

Hi Nick,

I've attached a file "formula help".

hopefully you can help! I feel a little silly I can't figure it out on my own, but that's why we have you guys! ;)


Nick's picture

Formula help - All done

Formula Help = All done


Your formula's were very helpful. I'm still getting an error on one and I wonder if it's because I am linking it to another worksheet?

here is the formula

=SUMPRODUCT(-('App Tracking 09'!C:C="REP"),-('App Tracking 09'!H:H="DENIED"),--('App Tracking 09'!F:F))

How my workbook looks

App tracking (one sheet), $$ Tracking (second sheet)

It is producting this error

I've attached the original file for your perusal so you can see all that I'm trying to track and how basic I've made the formulas thus far due to limited knowledge.

Nick's picture

Formula Help

for the sumproduct formula, you can't enter the entire column... make sure it's a range that doesn't include headings

Formula help


I made that change and now it's bring back a value, but the wrong value?

Sorry I'm such a pain here!

It's basically added all the REP options, not only the deleted ones.

Nick's picture

Formula help

=SUMPRODUCT(-('App Tracking 09'!C2:C5000="REP"),-('App Tracking 09'!H2:H5000="DENIED"),--('App Tracking 09'!F2:F5000))

- works fine for me with your original sheet.


Formula help

hmmm, Thanks it worked when I copy pasted yours, not sure what I was doing wrong!

Thanks again for your help I really appreciate it!

Honestly am I retarded?

Now I'm trying to use the same formula for the monthly breakdown.

=SUMPRODUCT(-('App Tracking 09'!$D$2:$D$5000="1"),-('App Tracking 09'!$I$2:$I$5000="approved"),-('App Tracking 09'!$G$2:$G$5000))

and I am getting a #VALUE! error.

What am I doing wrong now? I even tried to shorten the 2-5000 range and it didn't make a difference.

Nick's picture

Formula Help

firstly, you need to add another column called MONTH, and use the MONTH formula - input column D
This can go in column K... take a look at my first spreadsheet reply.

then u need:

=SUMPRODUCT(-('App Tracking 09'!K2:K5000=1),-('App Tracking 09'!H2:H5000="DENIED"),--('App Tracking 09'!F2:F5000))

first argument = the month column
2nd = Approved / Denied
3rd = the column you're summing