Sumif I have no clue

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Book1
ABCDEF
1
2
3
4LOCATION_CODE(All)
5
6GROSSDEACTSREPORT_DATE
7NAMEEMPLOYEE_IDWKH_DISC_REASON9/1/20069/3/20069/4/2006
8VariosInfo123456FN4
9JD1
10PG9
111234561
12VariosInfo234567DF8
13JD4
14234567
15VariosInfo345678PG8
16345678
17VariosInfo456789FN4
18RY6
19456789
20567890QW1
21LH5
22567890
Sheet1


SO thats my data, I would like another cell to count if c8:c5000=FN and if d7=(cell location) then add number is d8:5000

does that makes sense, why can't i figure this out?

i've tried sumif and sum(if

but i give up
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

this is not clear to me
especialy "if d7=(cell location)"
I suppose the formulas must be in column D ?
why do you have "4" in D8, "1" in D9, ...

kind regards,
Erik
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Hi,

this is not clear to me
especialy "if d7=(cell location)"
I suppose the formulas must be in column D ?
why do you have "4" in D8, "1" in D9, ...

kind regards,
Erik

The numbers in column D are the amount of FN, JD for that day for that person (various info)

d7 is the date that will change ie e7 etc
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

It's not very clear to me what you want to do.

It's like a sumif, sum a column of numbers based on whether column C is "FN" but the specific column to sum is determined by a date?

If that's right then with your date (e.g. 9/1/2006) in A1 try

=SUMIF(C8:C5000,"Fn",INDEX(D8:IV5000,0,MATCH(A1,D7:IV7,0)))
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
It's not very clear to me what you want to do.

It's like a sumif, sum a column of numbers based on whether column C is "FN" but the specific column to sum is determined by a date?

If that's right then with your date (e.g. 9/1/2006) in A1 try

=SUMIF(C8:C5000,"Fn",INDEX(D8:IV5000,0,MATCH(A1,D7:IV7,0)))

Hmmm, you make it sound sooo complicated. I must not be explaining properly.

But i believe you have the right idea,

Some the column if "FN" is located in the C column, however its determined by the date on row 9.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Sorry, I still don't get it

I don't see any date on row 9, do you mean row 7?

Where does the formula go, is it a single formula or one for each row or column?

If you just want to sum column D when column C is "FN"

=SUMIF(C8:C5000,"FN",D8:D5000)

...but I assumed it was more than that. If it is then can you give an example based on the above screenshot
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hmmm, you make it sound sooo complicated. I must not be explaining properly.

But i believe you have the right idea,

Some the column if "FN" is located in the C column, however its determined by the date on row 9.

it's impossible to find out any formula if you do not explain what it should do exactly
provide a simple-to-understand-example

1. in what cell do you put the formula (not so important)
2. where is it reading data from ? (display those data if you didn't already)
3. what is the expected result ?
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Sorry, I still don't get it

I don't see any date on row 9, do you mean row 7?

Where does the formula go, is it a single formula or one for each row or column?

If you just want to sum column D when column C is "FN"

=SUMIF(C8:C5000,"FN",D8:D5000)

...but I assumed it was more than that. If it is then can you give an example based on the above screenshot

my apologizes, the orginal worksheet i'm using has a date of row 9, but you are correct this one has it on row 7

I have used the sumif that you just provided, but it is not calculating the data. Does it matter this is all based on access file/pivot table?
 

Forum statistics

Threads
1,136,696
Messages
5,677,259
Members
419,682
Latest member
M3one

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top