If - Countifs formula to need correcting

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Mistake in my solution expected column. Hope someone understands. Will check later for any solutions. GTG
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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))))
 
Upvote 0
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
 
Upvote 0
etaf said:
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
Click to expand...
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
 
Upvote 0
RAJESH1960 said:
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
Click to expand...
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))
 
Upvote 0
RAJESH1960 said:
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))
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
98
etaf
etaf
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
563
juanam
J
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
368
kevin9999
K
G
  • Solved
Counting zero billing days in Excel until current date
Replies
5
Views
534
awoohaw
awoohaw
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
201
POLACOLOCO
P

Forum statistics

Threads
1,214,804
Messages
6,121,652
Members
449,045
Latest member
Marcus05

Share this page

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
Back
Top