Countif multiple criteria

[rajeshn_in]

Hi friends,


Please help me to solve below issue :

Please see attachment


In the attachment Contract no. is same, but invoice numbers are two, So I want "1" for first invoice rows and "2" for second invoice rows in C Column.

Thank you.

[TMS]

Looking at this on an iPad so I can't see your formula.

But, if there are multiple criteria, you need to us COUNTIFS not COUNTIF

[rajeshn_in]

I didn't use any formula in my sheet, I want formula for C column.

Thank you

[Bo_Ry]

Please try at C2
=SUMPRODUCT(1/COUNTIF(B$2:B2,B$2:B2))

or
=SUMPRODUCT(1/COUNTIFS(B$2:B2,B$2:B2,A$2:A2,A$2:A2))

[rajeshn_in]

Thank you very much

[rajeshn_in]

Dear Friends,

Please solve the below problem. I am unable to clear my problem with previous solution. I am very sorry for reopening this issue.

I want formulas to get same result for lot no. (C columns).


LOT NO.jpg

Thank you.

[AliGW]

Attach the new workbook.

[rajeshn_in]

Dear Aligw,

Previous post edited with attachment.

Thank you

[AliGW]

There are no expected outcomes and I don't know what you mean by the criteria.

Help us to help you!!!

[AliGW]

Please see the anomalies highlighted:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	CONTRACT NO.	INVOICE NO.	LOT NO.	CRITERIA
2	RG / JSSL / 0202 / 19	INV1022019	1	BOTH FIRST TIME
3	RG / JSSL / 1111 / 19	INV1022019	1	BOTH FIRST TIME	B is a repeat
4	RG / JSSL / 0202 / 19	INV1022019	1	BOTH REPEATED
5	RG / JSSL / 0202 / 19	INV1022019	1	BOTH REPEATED
6	RG / JSSL / 0202 / 19	INV1022020	2	A - SECOND TIME, B - FIRST TIME	A is a repeat

Sheet: Sheet1

You are going to have to explain what you mean, because your outcomes seem to be incorrect.

[rajeshn_in]

Contract No. & Invoice Nos. coming first time then Lot No. 1

Lot no should change only invoice number change for that contract no.

Repeated Contract & Invoice Nos (combo) should have same Lot no.

[AliGW]

Please provide your expected results.

[rajeshn_in]

My expected result is in C column.

[rajeshn_in]

Contract No. & Invoice Nos. coming first time then Lot No. 1


Lot no should change only invoice number change for that contract no.
OR
Lot no should change only contract no. change for that invoice no.


Repeated Contract & Invoice Nos (combo) should have same Lot no.

[AliGW]

This makes no sense to me at all:

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	CONTRACT NO.	INVOICE NO.	LOT NO.	CRITERIA
2	RG / JSSL / 0202 / 19	INV1022019	1	BOTH FIRST TIME
3	RG / JSSL / 1111 / 19	INV1022019	1	BOTH FIRST TIME

Sheet: Sheet1

I'm out - sorry.

[rajeshn_in]

For 0202 contract - Invoice coming first time
For 1111 contract - invoice coming first time

[AliGW]

Yes, precisely - so how can they BOTH be the first time in row 3?

[Glenn Kennedy]

I am struggling here. Open the sheet. Please justify the expected answer for the two sets of shaded cells.

[rajeshn_in]

Glenn Kennedy,

Please check attachment

[Glenn Kennedy]

There nothing there that explains what you are doing. All you have done is move the shading around a bit, as far as I can see!!! The values in column C seem to be unrelated to the comments in column D...

[Glenn Kennedy]

This half works... but does not deliver your expected results after row 8.

Explain with NEW words, the reasons why your expected results are NOT the ones shown in YELLOW in this sheet. Totally baffled.

[Bo_Ry]

Please try at C2
=SUMPRODUCT(1/COUNTIF(INDEX(B$2:B2,MATCH(A2,A$2:A2,)):B2,INDEX(B$2:B2,MATCH(A2,A$2:A2,)):B2))

[rajeshn_in]

Thank you Bo_Ry & Glenn Kennedy.

I will go with Bo_Ry solution.