Help figuring out a formula
Hi There,
I have a few questions:
EXAMPLE
A B C D
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
QUESTION 1
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
QUESTION 2
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.
Thanks,
Meagan
I can include my file if necesary
| Attachment | Size |
|---|---|
| App Tracking.xls | 501.5 KB |
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
Nick
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! ;)
Meagan
Formula help - All done
Download Formula help sheet here
Formula Help = All done
Thanks!
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
"#NUM!"
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.
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
Thanks,
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.
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.
returns:
26500
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.
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
Nick