Use a formula

[mevikasjain]

Use a formula in cell shaded in grey to sum and count the values mentioned in the range shaded in yellow based on the criterias mentioned in the cells shaded with blue
Note:Both the conditions i.e. Product and Invoice day should be satisfied before summing or counting the data


PRODUCT--- INVOICE DAY--- GROSS AMOUNT
CC--- 1--- 2468.65
CC--- 2--- 2.99
CC--- 11--- 2.99
CC--- 4--- 2.99
CC--- 7--- 2.99
CC--- 8--- 2.99
CC--- 11--- 2.99
CC--- 10--- 89.95
CC--- 11--- 99.95
SG--- 1--- 29.95
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 105.93
SG--- 1--- 2.99
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 2.99
SG--- 7--- 2.99
WI--- 8--- 2.99
WI--- 11--- 122.39

Product INVOICE DAY
CC 11

Sum Count


pls help me...
Note: --- represents only separation from columns

 

[mevikasjain]

pls help me in this...

 

[Ingolf]

I can't see the range shaded in yellow and the cells shaded with blue

 

[theozz]

I cannot see

shaded in grey
range shaded in yellow
shaded with blue ...

Want this???
PRODUCT = CC
INVOICE DAY = 1 , 4, etc...
answer is ?
GROSS AMOUNT???

 

[mevikasjain]

Use a formula in cell to sum and count the values mentioned in the range (product, inovice day and gross amount) based on the criterias mentioned in the cells shaded with blue
Note:Both the conditions i.e. Product and Invoice day should be satisfied before summing or counting the data


PRODUCT--- INVOICE DAY--- GROSS AMOUNT
CC--- 1--- 2468.65
CC--- 2--- 2.99
CC--- 11--- 2.99
CC--- 4--- 2.99
CC--- 7--- 2.99
CC--- 8--- 2.99
CC--- 11--- 2.99
CC--- 10--- 89.95
CC--- 11--- 99.95
SG--- 1--- 29.95
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 105.93
SG--- 1--- 2.99
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 2.99
SG--- 7--- 2.99
WI--- 8--- 2.99
WI--- 11--- 122.39

Product INVOICE DAY
CC 11


Sum

Count


pls help me...
Note: --- represents only separation from columns

 

[zaska]

Hi Vikas,

It seems that your question is Vague. It would be better if you could explain it clearly with example instead of using colors.

 

[mevikasjain]

Can any one Use a formula in cell to sum and count the values mentioned in the range based on the criteria mentioned
Note:Both the conditions i.e. Product and Invoice day should be satisfied before summing or counting the data


PRODUCT--- INVOICE DAY--- GROSS AMOUNT
CC--- 1--- 2468.65
CC--- 2--- 2.99
CC--- 11--- 2.99
CC--- 4--- 2.99
CC--- 7--- 2.99
CC--- 8--- 2.99
CC--- 11--- 2.99
CC--- 10--- 89.95
CC--- 11--- 99.95
SG--- 1--- 29.95
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 105.93
SG--- 1--- 2.99
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 2.99
SG--- 7--- 2.99
WI--- 8--- 2.99
WI--- 11--- 122.39


Product INVOICE DAY
CC 11

Sum Count



Can any one help.. like, if i guess the formula only product cc and invoice day 11 should be sum and Count
in another cell..

 

[Sanjeev1976]

Use a formula in cell to sum and count the values mentioned in the range (product, inovice day and gross amount) based on the criterias mentioned in the cells shaded with blue
Note:Both the conditions i.e. Product and Invoice day should be satisfied before summing or counting the data


PRODUCT--- INVOICE DAY--- GROSS AMOUNT
CC--- 1--- 2468.65
CC--- 2--- 2.99
CC--- 11--- 2.99
CC--- 4--- 2.99
CC--- 7--- 2.99
CC--- 8--- 2.99
CC--- 11--- 2.99
CC--- 10--- 89.95
CC--- 11--- 99.95
SG--- 1--- 29.95
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 105.93
SG--- 1--- 2.99
SG--- 2--- 2.99
SG--- 11--- 2.99
SG--- 4--- 2.99
SG--- 7--- 2.99
WI--- 8--- 2.99
WI--- 11--- 122.39

Product INVOICE DAY
CC 11


Sum

Count


pls help me...
Note: --- represents only separation from columns

If your data starts in A1 and the criterias are in row 24, use the below formula for sum =SUMPRODUCT(--(A2:A21=A24),--(B2:B21=B24),C2:C21) and for count =SUMPRODUCT(--(A2:A21=A24),--(B2:B21=B24))

Hope this is what you were looking for.

 

[mevikasjain]

thxs for ur reply... but i am unable to perform that action..

 

[chuckles1066]

I'm probably being cynical......but are you sitting an exam of some sort and blagging the answers off of here?

It's just that your requests are phrased like exam paper questions!