If - Countifs formula to need correcting

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
Hello guys,

I need a little help in altering and adding 2 more conditions in the formula. The formula has 2 corrections to be made. One is instead of giving the result as “Not in GST”, I want the same date derived from the formula as in “Tally” in place of “Not in GST”, as mentioned in the formula. The second one it is displaying a mistake when the amount in Tally is displayed above GST in all the rows.
I am trying to get the dates as per GST in all the rows and same date for the same invoice numbers in Tally.

query.xlsx
ABCDEFGHIJK
1ABCDNo.Get DatesDateAmountAs PerResult
21301NOT IN GST27-03-20217350.00TALLYCorrect
3211706-07-202006-07-202022050.00GSTCorrect
4311706-07-202008-07-202022050.00TALLYCorrect
5412709-07-202009-07-202022050.00GSTCorrect
6512709-07-202011-07-202022050.00TALLYCorrect
739551NOT IN GST19-09-202026775.00TALLYIf Not in GST then dislpay tally date 19-0-2020
84059419-09-202005-01-20212688.00TALLYMistakeHas to show 08-01-2021
94159408-01-202108-01-20212688.00GSTCorrect
10109120912-03-202012-03-20205264.00GSTCorrect
1114869431-03-202131-03-2021472.00GSTCorrect
1214943728-02-202128-02-20212006.00GSTCorrect
1315015017-03-202117-03-20216510.00GSTCorrect
14207263NOT IN GST22-05-20202822.00TALLYIf Not in GST then dislpay tally date 19-0-2020
1520875802-07-202002-07-20201050.00GSTCorrect
162093202-07-202002-07-20201050.00TALLYCorrect
172103226-10-202026-10-20201050.00GSTCorrect
1821175826-10-202026-10-20201050.00TALLYCorrect
19212200119-06-201919-06-20191956.00GSTCorrect
20213197911-06-201911-06-20192003.00GSTCorrect
21214249017-01-202017-01-20202100.00GSTCorrect
22401011117-01-202001-05-20212100.00TALLYMistakeHas to show 09-05-2021
23401111109-05-202109-05-20212100.00GSTCorrect
244012111NOT IN GST09-05-20212101.00TALLYCorrect
Exact Match
Cell Formulas
RangeFormula
F2:F24F2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F24Expression=COUNTIFS($E$2:$E$1264,F2)>1textNO
 
Last edited:

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
Etaf, going to hit the sack now. Hope you understood the question... Date in GST rows should be the same. I have to replace the dates of tally if the Invoice number and amount are the same then only the date should be replaced with the date of GST in the Tally row and not otherwise. Good Night
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,115
Office Version
  1. 365
Platform
  1. MacOS
not sure i know what to do here exactly
=IF(I8="GST",G8,IF(COUNTIFS(I:I,"GST",E:E,E8,H:H,H8),G7,"NOT IN GST"))
So this is working and returning G7
BUT you dont want that in row 8 you want G9 to be returned because it has GST in the row and a matching ID

is it just luck , because the way the data has been ordered that you are not getting more issues ?

I sorted the table by "NO." (E) and then by "AS PER" (I)
so that GST is above tally for the IDs -
As it would appear all your other data , has that order - maybe by luck
so is this table now giving the correct info - for the NON highlighted parts
Then that's probably the best way to deal with , which is simply sorting the data

See my column Q - same formula - but the data has been sorted E & I

Book1
ABCDEFGHIJKLMNOPQ
1ABCDNo.Get DatesDateAmountAs PerResult
22103210/26/2010/26/2011/15/02GSTCorrect10/26/2010/26/2010/26/20
32093210/26/207/2/2011/15/02TALLYCorrect10/26/2010/26/2010/26/20
440111115/6/215/6/219/30/05GSTCorrect5/6/215/6/215/6/21
540101115/6/215/1/219/30/05TALLYMistakeHas to show 06-05-20215/6/21Same Mistake5/6/215/6/21
64012111NOT IN GST5/7/2110/1/05TALLYIf Not in GST then dislpay tally date 07-05-20215/7/21NOT IN GSTNOT IN GST
721177/6/207/6/201/13/06GSTCorrect7/6/207/6/20L2:L24L27/6/20
831177/6/207/8/201/13/06TALLYCorrect7/6/207/6/20 Like Quote Reply7/6/20
941277/9/207/9/201/13/06GSTCorrect7/9/207/9/207/9/20
1051277/9/207/11/201/13/06TALLYCorrect7/9/207/9/207/9/20
111501503/17/213/17/2110/27/17GSTCorrect3/17/213/17/213/17/21
12207263NOT IN GST5/22/209/22/07TALLYIf Not in GST then dislpay tally date 22-05-20205/22/20NOT IN GSTNOT IN GST
131301NOT IN GST3/27/212/14/20TALLYCorrect3/27/21NOT IN GSTNOT IN GST
141494372/28/212/28/216/28/05GSTCorrect2/28/212/28/212/28/21
1539551NOT IN GST9/19/204/21/73TALLYIf Not in GST then dislpay tally date 19-09-20209/19/20NOT IN GSTNOT IN GST
16415941/8/211/8/215/11/07GSTCorrect1/8/211/8/211/8/21
17405941/8/211/5/215/11/07TALLYMistakeHas to show 08-01-20211/8/21Same Mistake1/8/2111/8/21
181486943/31/213/31/214/16/01GSTCorrect3/31/213/31/213/31/21
192087587/2/207/2/2011/15/02GSTCorrect7/2/207/2/207/2/20
202117587/2/2010/26/2011/15/02TALLYCorrect7/2/207/2/207/2/20
2110912093/12/203/12/205/30/14GSTCorrect3/12/203/12/203/12/20
2221319796/11/196/11/196/25/05GSTCorrect6/11/196/11/196/11/19
2321220016/19/196/19/195/9/05GSTCorrect6/19/196/19/196/19/19
2421424901/17/201/17/209/30/05GSTCorrect1/17/201/17/201/17/20
Sheet4
Cell Formulas
RangeFormula
P17P17=COUNTIFS(I:I,"GST",E:E,E17,H:H,H17)
F2:F24F2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
L2:L24L2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,IF(I2="Tally",G2,IF(COUNTIFS(I:I,"Tally",E:E,E2,H:H,H2),G2,G3))))
N2:N24N2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
Q2:Q24Q2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
 
Last edited:

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
not sure i know what to do here exactly
=IF(I8="GST",G8,IF(COUNTIFS(I:I,"GST",E:E,E8,H:H,H8),G7,"NOT IN GST"))
So this is working and returning G7
BUT you dont want that in row 8 you want G9 to be returned because it has GST in the row and a matching ID

is it just luck , because the way the data has been ordered that you are not getting more issues ?

I sorted the table by "NO." (E) and then by "AS PER" (I)
so that GST is above tally for the IDs -
As it would appear all your other data , has that order - maybe by luck
so is this table now giving the correct info - for the NON highlighted parts
Then that's probably the best way to deal with , which is simply sorting the data

See my column Q - same formula - but the data has been sorted E & I

Book1
ABCDEFGHIJKLMNOPQ
1ABCDNo.Get DatesDateAmountAs PerResult
22103210/26/2010/26/2011/15/02GSTCorrect10/26/2010/26/2010/26/20
32093210/26/207/2/2011/15/02TALLYCorrect10/26/2010/26/2010/26/20
440111115/6/215/6/219/30/05GSTCorrect5/6/215/6/215/6/21
540101115/6/215/1/219/30/05TALLYMistakeHas to show 06-05-20215/6/21Same Mistake5/6/215/6/21
64012111NOT IN GST5/7/2110/1/05TALLYIf Not in GST then dislpay tally date 07-05-20215/7/21NOT IN GSTNOT IN GST
721177/6/207/6/201/13/06GSTCorrect7/6/207/6/20L2:L24L27/6/20
831177/6/207/8/201/13/06TALLYCorrect7/6/207/6/20 Like Quote Reply7/6/20
941277/9/207/9/201/13/06GSTCorrect7/9/207/9/207/9/20
1051277/9/207/11/201/13/06TALLYCorrect7/9/207/9/207/9/20
111501503/17/213/17/2110/27/17GSTCorrect3/17/213/17/213/17/21
12207263NOT IN GST5/22/209/22/07TALLYIf Not in GST then dislpay tally date 22-05-20205/22/20NOT IN GSTNOT IN GST
131301NOT IN GST3/27/212/14/20TALLYCorrect3/27/21NOT IN GSTNOT IN GST
141494372/28/212/28/216/28/05GSTCorrect2/28/212/28/212/28/21
1539551NOT IN GST9/19/204/21/73TALLYIf Not in GST then dislpay tally date 19-09-20209/19/20NOT IN GSTNOT IN GST
16415941/8/211/8/215/11/07GSTCorrect1/8/211/8/211/8/21
17405941/8/211/5/215/11/07TALLYMistakeHas to show 08-01-20211/8/21Same Mistake1/8/2111/8/21
181486943/31/213/31/214/16/01GSTCorrect3/31/213/31/213/31/21
192087587/2/207/2/2011/15/02GSTCorrect7/2/207/2/207/2/20
202117587/2/2010/26/2011/15/02TALLYCorrect7/2/207/2/207/2/20
2110912093/12/203/12/205/30/14GSTCorrect3/12/203/12/203/12/20
2221319796/11/196/11/196/25/05GSTCorrect6/11/196/11/196/11/19
2321220016/19/196/19/195/9/05GSTCorrect6/19/196/19/196/19/19
2421424901/17/201/17/209/30/05GSTCorrect1/17/201/17/201/17/20
Sheet4
Cell Formulas
RangeFormula
P17P17=COUNTIFS(I:I,"GST",E:E,E17,H:H,H17)
F2:F24F2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
L2:L24L2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,IF(I2="Tally",G2,IF(COUNTIFS(I:I,"Tally",E:E,E2,H:H,H2),G2,G3))))
N2:N24N2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
Q2:Q24Q2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,"NOT IN GST"))
It worked in the sample data. Let me check in the database and see as it contains more than 4000 rows. It will take time to check . Will get back to you whatever the result. Checked with the below formula and it is working. Hopefully works in the database. Fingers Crossed.
=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,IF(I2="Tally",G2,IF(COUNTIFS(I:I,"Tally",E:E,E2,H:H,H2),G2,G3))))
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
It worked in the sample data. Let me check in the database and see as it contains more than 4000 rows. It will take time to check . Will get back to you whatever the result. Checked with the below formula and it is working. Hopefully works in the database. Fingers Crossed.
=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,IF(I2="Tally",G2,IF(COUNTIFS(I:I,"Tally",E:E,E2,H:H,H2),G2,G3))))
It is no good. In the first 100 rows itself there are so many mismatches. 😒
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,115
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

what is no good - the sort ?
post the sample , as the sort worked on your sample - so clearly something is different from the sample compared to the real data
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
what is no good - the sort ?
post the sample , as the sort worked on your sample - so clearly something is different from the sample compared to the real data
I am making a list of few unmatched ledgers and copying them to a new sheet to show you. Please give me sometime. Before that I want to check for if I have made any mistake. Will not be able to do it like soon. Will message you as soon as I finish it. Thanks etaf.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
I am making a list of few unmatched ledgers and copying them to a new sheet to show you. Please give me sometime. Before that I want to check for if I have made any mistake. Will not be able to do it like soon. Will message you as soon as I finish it. Thanks etaf.
Etaf. When I sorted the sample sheet the invoice numbers were the same but in the data sheet the invoice number can be same in many cases. Even amounts are common in different cases. Hence it is not a 100% match.
The invoice numbers with a unique ID are a perfect match. 100% of them have matched. I will have to try a different way to find any other unique value and try to solve it next time.
I am sharing the sheet for your reference. These are just a sample of the 4000 and odd rows.
Etaf Test.xlsx
ABCDEFGHIJKL
1LineABCInvoice numberGet DateDateAmountAs PerRemarksActual Date to get
230106-05-202006-05-20201630.00TALLY
371119-05-202019-05-20206013.00TALLY
431208-05-202008-05-2020288.00TALLY
51440302-06-202002-06-202014303.00GST
6257314-10-201914-10-2019139776.00GST
71441314-10-201902-06-202014303.00TALLYmistake02-06-2020as invoice no 3 in row 5 is a match with invoice no.3 in row 7
832309-05-202009-05-202021143.00TALLY
91569515-06-202015-06-202036590.40GST
101570515-06-202015-06-202036590.40TALLY
111689716-07-202016-07-202043243.00GST
1233712-05-202012-05-20202421.00TALLY
131690712-05-202016-07-202043243.00TALLYmistake16-07-2020
1434813-05-202013-05-202011400.00TALLY
15101813-03-202113-03-202125200.00TALLY
161432908-06-202008-06-202010014.00GST
171433908-06-202008-06-202010014.00TALLY
1814341010-06-202010-06-202010924.00GST
1914351010-06-202010-06-202010924.00TALLY
2012931108-06-202008-06-202043536.00GST
21351116-05-202016-05-20201270.00TALLY
2212941116-05-202008-06-202043536.00TALLYmistake08-06-2020
23361218-05-202018-05-202010107.00TALLY
24371319-05-202019-05-20202940.00TALLY
259541404-09-202004-09-20202950.00GST
2625361406-11-201906-11-2019102312.00GST
2725371406-11-201906-11-2019102312.00GST
289551406-11-201904-09-20202950.00TALLYmistake04-09-2020
29691425-05-202025-05-202012878.00TALLY
30381519-05-202019-05-2020578.00TALLY
3115711602-07-202002-07-202039816.00GST
32391620-05-202020-05-20203667.00TALLY
3315721620-05-202002-07-202039816.00TALLYmistake02-07-2020
34971702-05-202002-05-20206510.00TALLY
35401820-05-202020-05-202021178.00TALLY
3611841910-06-202010-06-20208784.00GST
3726001922-06-202022-06-202011025.00GST
38701928-05-202028-05-20202185.00TALLY
3911851928-05-202010-06-20208784.00TALLYmistake10-06-2020
4026011910-06-202030-06-202011025.00TALLYmistake22-09-2020
412656B105516-12-202016-12-202023625.00GSTPerect Match
422657B105516-12-202016-12-202023625.00TALLYPerect Match
432664B-236508-10-202008-10-202015502.00GSTPerect Match
442665B-236508-10-202010-10-202015502.00TALLYPerect Match
452660B-252402-11-202002-11-202015060.00GSTPerect Match
462663B-252402-11-202004-12-202015060.00TALLYPerect Match
472666B-273801-12-202001-12-202016120.00GSTPerect Match
482667B-273801-12-202001-12-202016120.00TALLYPerect Match
492661B-278404-12-202004-12-202015060.00GSTPerect Match
502662B-278404-12-202002-11-202015060.00TALLYPerect Match
512246BLR/99/20-2129-05-202029-05-202017826.00GSTPerect Match
522247BLR/99/20-2129-05-202029-05-202017826.00TALLYPerect Match
53264BOM3-1862204-03-202004-03-2020269.00GSTPerect Match
54105Cat/13601-03-202101-03-202182320.00TALLYPerect Match
552550CBE012307-07-202007-07-20202950.00GSTPerect Match
562551CBE012307-07-202001-07-20202950.00TALLYPerect Match
5765DF-971316-07-202016-07-202030902.00GSTPerect Match
5866DF-971316-07-202016-07-202030902.00TALLYPerect Match
59190DL1202103BB5615017-03-202117-03-20216510.00GSTPerect Match
602410DPI/20-21/135917-08-202017-08-20207437.00GSTPerect Match
612411DPI/20-21/135917-08-202017-08-20207437.00TALLYPerect Match
62135GST-20/21/012709-07-202009-07-202022050.00GSTPerect Match
63136GST-20/21/012709-07-202008-07-202022050.00TALLYPerect Match
64474HO-26201-07-202001-07-202015120.00GSTPerect Match
65475HO-26201-07-202001-07-202015120.00TALLYPerect Match
66476HO-48820-07-202020-07-202031500.00GSTPerect Match
67477HO-48820-07-202001-10-202031500.00TALLYPerect Match
Match GST + Tally
Cell Formulas
RangeFormula
F65:F66,F52:F60,F42:F49,F2:F39F2=IF(I2="GST",G2,IF(COUNTIFS(I:I,"GST",E:E,E2,H:H,H2),G1,IF(I2="Tally",G2,IF(COUNTIFS(I:I,"Tally",E:E,E2,H:H,H2),G2,G3))))
F40,F67,F63,F61,F50F40=IF(I40="GST",G40,IF(COUNTIFS(I:I,"GST",E:E,E40,H:H,H40),G39,IF(I40="Tally",G40,IF(COUNTIFS(I:I,"Tally",E:E,E40,H:H,H40),G40,#REF!))))
F41,F64,F62,F51F41=IF(I41="GST",G41,IF(COUNTIFS(I:I,"GST",E:E,E41,H:H,H41),#REF!,IF(I41="Tally",G41,IF(COUNTIFS(I:I,"Tally",E:E,E41,H:H,H41),G41,G42))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F67Expression=COUNTIFS($E$2:$E$1310,F2)>1textNO

And thank you so much for your time buddy.
 

Forum statistics

Threads
1,148,277
Messages
5,745,813
Members
423,980
Latest member
zimza

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