Count Cells depending upon Multiple cell Values -

[chippymark]

Ok well here goes, for those of you advanced I will try to do my best to get across what I am looking to achieve: Please Bear with me.
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set,
So I'd expect E2 in the case below to read 1.83, But I would not want double postings ????
Is this feasable or am I expecting too much.

thanks in advance

Mark

Order No OP Posted Set Allowed Set
30761157 0010 0.73 1.500
30761157 0010 0.50 0.000
30761157 0010 0.60 0.000
30764610 0010 0.00 0.000
30764610 0010 1.05 0.500
30765244 0010 0.00 0.000
30765244 0010 0.55 0.500
30766396 0010 0.00 0.000
30766396 0010 1.18 0.500
30768109 0010 0.00 0.000
30768629 0010 0.00 0.000
30768629 0010 0.00 0.000
30768629 0010 0.00 0.000

 

[ADAMC]

Hi Mark,

I THINK your looking for a sumif.....
find all the part numbers, add all the posted values....!?

Anyway this formula in E2 returns: 1.83 it assumes order number is in column A and value to sum is in column D change accordingly

=SUMIF(A:A,30761157,D:D)


Let me know if this is no good and perhaps try to explain a little more

 

[chippymark]

Hi Adam, thanks for your reply, but I could not get that to work.
Basically I am trying a multiple argument,
Let me expalin further
If in cell D1 there is a value greater than Zero I want to count all the values in C cells where all the other A cells are equal to A1 and B cells are Equal to B1 ???
Does that make sense... ??

I am not 100% Clued up on excel, but I am getting there.
thanks Again

Mark

 

[chippymark]

Adam,
I am having another play with the Sum If and it is really close...
it needs to check that the Op number 0010 matches too...
and could only calculate if D > 0

I guess if will be something along the line of
If d>0 then sum if a:a = A1 and b:b=b1, e:e

Not sure how I would formulate that ????

Cheers
Mark.

 

[ADAMC]

Does that make sense...??

Nope not quite! ....In your example above column B is all the same...010 or am way off...

Does this sound like it could be right (Not a formula just establishing what we need)

IF D1 IS GREATER THAN ZERO, COUNT THE NUMBER OF TIMES 30761157 APPEARS IN COLUMN A BUT ONLY IF B? IS EQUAL TO B1?....

 

[ADAMC]

How about:

=IF(D1>0,IF(B1=10,SUMIF(A:A,A1,C:C)))
fill down column E................?

If criteria not met will return false..
So if D1 is 0 or B1 is not equal to 0010.....

Getting anywhere

 

[ADAMC]

Heres all data you provided in a worksheet:

Book1
	A	B	C	D	E
1	heading1	heading2	heading3	heading4	formula
2	30761157	0010	0.73	1.5000	1.83
3	30761157	0010	0.50	0.0000	FALSE
4	30761157	0010	0.60	0.0000	FALSE
5	30764610	0010	0.00	0.0000	FALSE
6	30764610	0010	1.05	0.5000	1.05
7	30765244	0010	0.00	0.0000	FALSE
8	30765244	0010	0.55	0.5000	0.55
9	30766396	0010	0.00	0.0000	FALSE
10	30766396	0010	1.18	0.5000	1.18
11	30768109	0010	0.00	0.0000	FALSE
12	30768629	0010	0.00	0.0000	FALSE
13	30768629	0010	0.00	0.0000	FALSE
14	30768629	0010	0.00	0.0000	FALSE
Sheet1

Now i apply a simple filter to formula column an delete all those which show false (as the condition is not met)

I am left with only values where D1 was greater than Zero and B1 was equal to 0010:

Book1
	A	B	C	D	E
1	heading1	heading2	heading3	heading4	formula
2	30761157	0010	0.73	1.5000	0.73
3	30764610	0010	1.05	0.5000	1.05
4	30765244	0010	0.55	0.5000	0.55
5	30766396	0010	1.18	0.5000	1.18
Sheet1

 

[chippymark]

Thanks again Adam,
We are very close to the desired results...
if heading 4 >0 add together all the values in Heading C that has the same heading 1 and heading 2>

In my example heading 2 is alway 0010 but it is quite often 0020 and 0030
so somewhere in my count if statement, it has to check that this too is the same as the others.

Sorry to be a pain, but thanks a million, I already have a little to be working with and my data is now more accurate

Cheers

mark..

 

[barry houdini]

Hello Mark,

Try this

Book1
	A	B	C	D	E
1	Order No	OP	Posted Set	Allowed Set
2	30761157	0010	0.73	1.500	1.83
3	30761157	0010	0.50	0.000
4	30761157	0010	0.60	0.000
5	30764610	0010	0.00	0.000
6	30764610	0010	1.05	0.500	1.05
7	30765244	0010	0.00	0.000
8	30765244	0010	0.55	0.500	0.55
9	30766396	0010	0.00	0.000
10	30766396	0010	1.18	0.500	1.18
11	30768109	0010	0.00	0.000
12	30768629	0010	0.00	0.000
13	30768629	0010	0.00	0.000
14	30768629	0010	0.00	0.000
15
Sheet3

Formula in E2 copied down

=IF(D2>0,SUMPRODUCT(--(A$2:A$14=A2),--(B$2:B$14=B2),C$2:C$14),"")

 

[ADAMC]

getting close?

=IF(D2>0,IF(B2=$B$2,SUMIF(A:A,A2,C:C)))

Book1
	A	B	C	D	E
1	OP	PostedSet	AllowedSet
2	30761157	10	0.73	1.5	1.83
3	30761157	10	0.5	0	FALSE
4	30761157	10	0.6	0	FALSE
5	30764610	10	0	0	FALSE
6	30764610	10	1.05	0.5	1.05
7	30765244	10	0	0	FALSE
8	30765244	10	0.55	0.5	0.55
9	30766396	10	0	0	FALSE
10	30766396	10	1.18	0.5	1.18
11	30768109	10	0	0	FALSE
12	30768629	10	0	0	FALSE
13	30768629	10	0	0	FALSE
14	30768629	10	0	0	FALSE
Sheet1

.............?