# Count Cells depending upon Multiple cell Values -

#### chippymark

##### Board Regular
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.

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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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.

##### Well-known Member
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?....

##### Well-known Member

=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

##### Well-known Member
Heres all data you provided in a worksheet:
Book1
ABCDE
23076115700100.731.50001.83
33076115700100.500.0000FALSE
43076115700100.600.0000FALSE
53076461000100.000.0000FALSE
63076461000101.050.50001.05
73076524400100.000.0000FALSE
83076524400100.550.50000.55
93076639600100.000.0000FALSE
103076639600101.180.50001.18
113076810900100.000.0000FALSE
123076862900100.000.0000FALSE
133076862900100.000.0000FALSE
143076862900100.000.0000FALSE
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
ABCDE
23076115700100.731.50000.73
33076461000101.050.50001.05
43076524400100.550.50000.55
53076639600101.180.50001.18
Sheet1

#### chippymark

##### Board Regular
We are very close to the desired results...

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

##### MrExcel MVP
Hello Mark,

Try this
Book1
ABCDE
1Order NoOPPosted SetAllowed Set
23076115700100.731.5001.83
33076115700100.500.000
43076115700100.600.000
53076461000100.000.000
63076461000101.050.5001.05
73076524400100.000.000
83076524400100.550.5000.55
93076639600100.000.000
103076639600101.180.5001.18
113076810900100.000.000
123076862900100.000.000
133076862900100.000.000
143076862900100.000.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),"")

##### Well-known Member
getting close?

=IF(D2>0,IF(B2=\$B\$2,SUMIF(A:A,A2,C:C)))
Book1
ABCDE
1OPPostedSetAllowedSet
230761157100.731.51.83
330761157100.50FALSE
430761157100.60FALSE
5307646101000FALSE
630764610101.050.51.05
7307652441000FALSE
830765244100.550.50.55
9307663961000FALSE
1030766396101.180.51.18
11307681091000FALSE
12307686291000FALSE
13307686291000FALSE
14307686291000FALSE
Sheet1

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

1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

### 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.

### Which adblocker are you using?

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

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