If - Countifs formula to need correcting

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
Mistake in my solution expected column. Hope someone understands. Will check later for any solutions. GTG
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
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-09-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 22-05-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 06-05-2021
23401111106-05-202106-05-20212100.00GSTCorrect
244012111NOT IN GST07-05-20212101.00TALLYIf Not in GST then dislpay tally date 07-05-2021
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"))
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
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-09-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 22-05-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 06-05-2021
23401111106-05-202106-05-20212100.00GSTCorrect
244012111NOT IN GST07-05-20212101.00TALLYIf Not in GST then dislpay tally date 07-05-2021
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
 

RAJESH1960

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

ADVERTISEMENT

Hello ..Anybody home.? Sorry Yesterday I had a wi-fi connectivity problem. When I saw my message I though I it was not sent and sent again. Message No.#3 is the actual problem.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
Helloooooooo, Where are you guys.? I got one problem solved with the formula but I am still getting 2 mistakes. Please help me to solve it.
query.xlsx
ABCDEFGHIJKLM
1ABCDNo.Get DatesDateAmountAs PerResult
21301NOT IN GST27-03-20217350.00TALLYCorrect27-03-2021
3211706-07-202006-07-20202205.00GSTCorrect06-07-2020
4311706-07-202008-07-20202205.00TALLYCorrect06-07-2020
5412709-07-202009-07-20202205.00GSTCorrect09-07-2020
6512709-07-202011-07-20202205.00TALLYCorrect09-07-2020
739551NOT IN GST19-09-202026775.00TALLYIf Not in GST then dislpay tally date 19-09-202019-09-2020
84059419-09-202005-01-20212688.00TALLYMistakeHas to show 08-01-202119-09-2020Same Mistake
94159408-01-202108-01-20212688.00GSTCorrect08-01-2021
10109120912-03-202012-03-20205264.00GSTCorrect12-03-2020
1114869431-03-202131-03-2021472.00GSTCorrect31-03-2021
1214943728-02-202128-02-20212006.00GSTCorrect28-02-2021
1315015017-03-202117-03-20216510.00GSTCorrect17-03-2021
14207263NOT IN GST22-05-20202822.00TALLYIf Not in GST then dislpay tally date 22-05-202022-05-2020
1520875802-07-202002-07-20201050.00GSTCorrect02-07-2020
162093202-07-202002-07-20201050.00TALLYCorrect02-07-2020
172103226-10-202026-10-20201050.00GSTCorrect26-10-2020
1821175826-10-202026-10-20201050.00TALLYCorrect26-10-2020
19212200119-06-201919-06-20191956.00GSTCorrect19-06-2019
20213197911-06-201911-06-20192003.00GSTCorrect11-06-2019
21214249017-01-202017-01-20202100.00GSTCorrect17-01-2020
22401011117-01-202001-05-20212100.00TALLYMistakeHas to show 06-05-202117-01-2020Same Mistake
23401111106-05-202106-05-20212100.00GSTCorrect06-05-2021
244012111NOT IN GST07-05-20212101.00TALLYIf Not in GST then dislpay tally date 07-05-202107-05-2021
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"))
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))))
 

etaf

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

ADVERTISEMENT

not sure what the issue is
In ROW 8
=IF(I8="GST",G8,IF(COUNTIFS(I:I,"GST",E:E,E8,H:H,H8),G7,"NOT IN GST"))
Has to show 08-01-2021
formula in F2
COUNTIFS(I:I,"GST",E:E,E8,H:H,H8)
Which will be true for anything over 0
in this case its 1
and so TRUE
which means put G7 into the cell
G7 contains
19/09/2020

and thats what you have , so the formula is working

No idea where
Has to show 08-01-2021
comes from
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
not sure what the issue is
In ROW 8
=IF(I8="GST",G8,IF(COUNTIFS(I:I,"GST",E:E,E8,H:H,H8),G7,"NOT IN GST"))
Has to show 08-01-2021
formula in F2
COUNTIFS(I:I,"GST",E:E,E8,H:H,H8)
Which will be true for anything over 0
in this case its 1
and so TRUE
which means put G7 into the cell
G7 contains
19/09/2020

and thats what you have , so the formula is working

No idea where
Has to show 08-01-2021
comes from
Row 8 And Row 9 are the matched rows because when the invoices are a match the date should be that of GST not Tally
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
Row 8 And Row 9 are the matched rows because when the invoices are a match the date should be that of GST not Tally
I got one of the answers from someone else but used an Filter excel 365 I think. and mine is Excel 2019. See if you can change this and make it work in my sheet. IF(I2="GST",G2,IF(I2="TALLY",IFERROR(AGGREGATE(14,4,FILTER(E:I,(E:E=E2)*(H:H=H2)*(I:I="GST")),1),G2),0))
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
685
Office Version
  1. 2019
Platform
  1. Windows
I got one of the answers from someone else but used an Filter excel 365 I think. and mine is Excel 2019. See if you can change this and make it work in my sheet. IF(I2="GST",G2,IF(I2="TALLY",IFERROR(AGGREGATE(14,4,FILTER(E:I,(E:E=E2)*(H:H=H2)*(I:I="GST")),1),G2),0))
What is I am trying to do is, the date in GST are right and the dates in Tally may or maynot be wrong. So I am replacing all the dates of tally matching with GST with the same number of invoice and amount which are common in both the cases and replacing the date of tally with the date of GST if they are a match. If they the invoice and amount are not a match then the date of tally should remain the same, The dates of GST should remain the same. It is fixed.
 

Forum statistics

Threads
1,144,278
Messages
5,723,465
Members
422,498
Latest member
KAT112014

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