Count Cells depending upon Multiple cell Values -

chippymark

Board Regular
Joined
Aug 2, 2007
Messages
50
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 :biggrin:

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

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Aug 2, 2007
Messages
50
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 :biggrin:
 

chippymark

Board Regular
Joined
Aug 2, 2007
Messages
50
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Heres all data you provided in a worksheet:
Book1
ABCDE
1heading1heading2heading3heading4formula
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
1heading1heading2heading3heading4formula
23076115700100.731.50000.73
33076461000101.050.50001.05
43076524400100.550.50000.55
53076639600101.180.50001.18
Sheet1
 

chippymark

Board Regular
Joined
Aug 2, 2007
Messages
50
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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),"")
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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


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

Forum statistics

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